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.
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.