macro copy specific columns to sheet2

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

oddworld

Board Regular
Joined
May 31, 2005
Messages
248
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,127,082
Messages
5,622,588
Members
415,909
Latest member
vbaBeginner94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top