anyone like to try and condense this code.....

[RET79]

Here is the first part of this macro I wrote at work, take a look, if you can help me condense the code it would cool, thanks. All it is doing is extracting isolated values from columns B,C,D of a monthly spreadsheet, then E,F,G, etc. in groups of three. But, I need to do this for every month and get all the data together in a nice little table. Anyway, you will see what I mean from the code - it works fine but just needs to be more efficient and flexible if possible. Thanks...


Public Sub UKGrowth()

'opens files for jan, feb, mar in turn, extracts the require data, then closes file.

B = Array("jan", "feb", "mar")

For Month = 0 To 2 Step 1
SourceBook = "dataForMonth" & B(Month) & ".xls"

Workbooks.Open Filename:="C:dataForMonth" &
B(Month) & ".xls"

Set Source = Application.Workbooks(SourceBook).Worksheets("UK Growth")
For i = 0 To 32 Step 1

Result.Cells(2, 1).Offset(Month * 45 + i, 0).Value = Source.[B3].Offset(-1,
3 * i).Value

Result.Cells(2, 2).Offset(Month * 45 + i, 0).Value = Source.Cells(4,
4).Offset(0, 3 * i).Value
Result.Cells(2, 3).Offset(Month * 45 + i, 0).Value = Source.Cells(24,
2).Offset(0, 3 * i).Value
Result.Cells(2, 4).Offset(Month * 45 + i, 0).Value = Source.Cells(24,
3).Offset(0, 3 * i).Value
Result.Cells(2, 5).Offset(Month * 45 + i, 0).Value = Source.Cells(72,
2).Offset(0, 3 * i).Value
Result.Cells(2, 6).Offset(Month * 45 + i, 0).Value = Source.Cells(89,
2).Offset(0, 3 * i).Value
Result.Cells(2, 7).Offset(Month * 45 + i, 0).Value = Source.Cells(103,
2).Offset(0, 3 * i).Value
Result.Cells(2, .Offset(Month * 45 + i, 0).Value = Source.Cells(114,
2).Offset(0, 3 * i).Value
Result.Cells(2, 9).Offset(Month * 45 + i, 0).Value = Source.Cells(196,
4).Offset(0, 3 * i).Value
Result.Cells(2, 10).Offset(Month * 45 + i, 0).Value = Source.Cells(220,
4).Offset(0, 3 * i).Value
Result.Cells(2, 11).Offset(Month * 45 + i, 0).Value = Source.Cells(44,
4).Offset(0, 3 * i).Value
Next i

Workbooks(SourceBook).Close False

Next Month

End Sub.

Any comments appreciated. Thanks.

 

[RET79]

The big chunk in the code above looks frightening, but all I am doing is this:

Taking the values of D4, B24, C24 etc. and putting them in Cells(2,1) to Cells(2,10) of another workbook, which I refer to as results.

Then put G4, E24, F24 etc. and put them into Cells(3,1) to Cells(3,10)

So what I need is a slick way, ideally a one liner to put isolated cells into a range. something like this, but I am not sure of how to code it exactly

Range("A2:K2").value = Range("D4", "B24", "C24", ......).value

and then put in an offsets for the rest.

Hope that makes some sense.
Any help appreciated!

 

[Anne Troy]

I want to know what function the smiley face calls.

 

[RET79]

:wink:

 

[RET79]

So, has someone any ideas about this question??

Thanks