PDA

View Full Version : Help Consolidating Data Please



prb18
12-01-2013, 09:22 PM
Hi,

I am very new to VBA and it would be very useful for what I need to do. I am running 2007 excel I have two workbooks, the first containing 9 worksheets and the second containing 11 worksheets. The worksheets are numbered individually by year, 1982-1990 and 1991-2001. The setup for all the worksheets is the same, the data range for each worksheet is C5:GM195, and all the worksheets contain information on different years and the countries are listed the same way in each worksheet. I need to copy the data from all the worksheets into 1 worksheet, and the data needs to be organized by country, as it already is in the separate worksheets, and chronologically by year as well. So, for the 'master' worksheet, the data range for country A will be C5:GM5 for year 1982, the row beneath that will be range C6:GM6 for country A for year 1983. Each country has data up to year 2001 and there are 202 countries in total. Any help would be greatly appreciated, as I am just finding out how useful VBA can be

Bob Phillips
12-02-2013, 08:23 AM
Air code, assuming the two workbooks are open, and you have a third empty active workbook to drop it all into



Sub MergeData()

With Workbooks("the first workbook name")

nextrow = 1
For i = 1982 To 1990

.Worksheets(i).Range("C5:GM195").Copy ActiveWorkbook.Worksheets(1).Cells(nextrow, "C")
.Cells(i, "B").Resize(191) = i
nextrow = nextrow + 191
Next i
End With

With Workbooks("the first workbook name")

For i = 1991 To 2001

.Worksheets(i).Range("C5:GM195").Copy ActiveWorkbook.Worksheets(1).Cells(nextrow, "C")
.Cells(i, "B").Resize(191) = i
nextrow = nextrow + 191
Next i
End With

With ActiveWorkbook

.Range("C5:GM5").Resize(20 * 191).Sort key1:=.Range("B5"), order1:=xlAscending, _
key2:=.Range("C5"), order2:=xlAscending, _
Headers:=xlNo
End With
End Sub

prb18
12-02-2013, 09:55 PM
Thank you for your help, it is much appreciated.