macro copy specific columns to sheet2

[oddworld]

hi all, i would appreciate some asistance with the following issue? I have a workbook with 10 sheets, the sheets all have the same heading row (1:1), however the locations are different (i can't control this issue). So I have tried to use a macro to specifical copy 4 columns of data from all of the sheets and past them into sheet 2.

I am using the below code, it is only copying the columns from the last sheet instead of all of them. And how can i get it to create sheet 2 as the first set. I would need the data from each sheet to be pasted neatley under each other on sheet2.

Sub Copy_specific_colums()
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("sheet2").Range("A1")
ws.Columns(ACTL_UNIT).Copy Destination:=Sheets("sheet2").Range("B1")
ws.Columns(RULE_ID).Copy Destination:=Sheets("sheet2").Range("C1")
ws.Columns(SERVICE).Copy Destination:=Sheets("sheet2").Range("D1")

Next ws

End Sub


Regards,
Odd

 

[oddworld]

ok, so i now understand that my code is copying all the sheets data into sheet2, but its over writing each sheets data, ie the data that is copied in sheet2 is the 10th sheet, all other sheets data is over written. i would like some help with aggregating all the sheets data in sheet 2. The last sheet has 611 rows of data and thats what is in sheet 2 at completion of the code, i moved sheet 3 to the end containing 12 rows of data and and ran the code and thats what goes into sheets 2. so i just need it aggregated it all together.

Regards,

Odd