hi all i have a workbook containing 10 sheets. each sheet has a header row, however the headings are not in the same order (out of my control). I am working with the below code, its copying my specific columns of data to the master sheet"Master", but it is over writing each sheets data until the last sheet. I would appreciate some assistance in looping through and aggregating all 10 sheets worth of data (of the 4 specific columns) being pasted into the master sheet. So the 10 sheets combined should amount to 1500 rows of data in 4 columns.The 4 column heading exist in each sheet but in different locations.
Code:
[/SIZE]
Sub Copy_specific_columns()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
EMPLID = WorksheetFunction.Match("EMPLID", Rows("1:1"), 0)
ACTL_UNIT = WorksheetFunction.Match("ACTL_UNIT", Rows("1:1"), 0)
RULE_ID = WorksheetFunction.Match("RULE_ID", Rows("1:1"), 0)
SERVICE = WorksheetFunction.Match("SERVICE", Rows("1:1"), 0)
ws.Columns(EMPLID).Copy Destination:=Sheets("Master").Range("A1")
ws.Columns(ACTL_UNIT).Copy Destination:=Sheets("Master").Range("B1")
ws.Columns(RULE_ID).Copy Destination:=Sheets("Master").Range("C1")
ws.Columns(SERVICE).Copy Destination:=Sheets("Master").Range("D1")
Next ws
End Sub
Regards,
Odd