Macro to copy data from multiple worksheets into 1 worksheet

J

jillibillijames

Board Regular
Joined
Apr 19, 2011
Messages
66
I have a spreadsheet that has hundreds of different worksheets. Within each worksheet there is a multiple row of data from cell A1 to K2.

I want to copy this rows from each worksheet into a single worksheet.

Any help on how I can build a macro to loop through each worksheet and do this would be greatly appreciated.

Thanks for your help.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
Hi,

I tried with this macro, but it could only copy one row and i am looking for

multiple roes to be copied. I changed the range and tried running the same... i

din't work.

Can you plz help me out.

Regards
james
 
Upvote 0
Hi jillibillijames,

If it is only Rows 1 & 2 form the data sheets that you want copied to the Master Sheet.

Make the Master Sheet the first sheet and name it "Master", then, if all the other sheets are data sheets, then this code will do it.

It assumes there is a header row on the Master sheet so starts copying to row 2.

Code: 
Sub CopyDataToMaster()
Sheets("Master").Activate
For a = 2 To Sheets.Count
    For b = 1 To 2
        Sheets(a).Rows(b & ":" & b).Copy
        R = Sheets("Master").Range("A65536").End(xlUp).Row + 1
        Sheets("Master").Cells(R, 1).Select
        ActiveSheet.Paste
    Next b
Next a
End Sub

I'm sure there are many on the forum who can come up with a more concise code, but this will do it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA to Split Data From an Existing Table into Another Single Worksheet with Header for Each Section
Replies
1
Views
154
John_w
J
A
  • Solved
Opening worksheets using worksheet names in a named range
Replies
4
Views
251
anythinggoes3949
A
J
  • Question
Macro to copy values from multiple sheets in a workbook to a new worksheet in the same workbook
Replies
3
Views
181
MARK858
MARK858
J
  • Question
Macro for copying data from multiple sheets to a common sheet based on a cell value
Replies
0
Views
205
jmk15315
J
B
  • Solved
Find column and delete rows below....
2
Replies
11
Views
544
Alex Blakenburg
A

Forum statistics

Threads
1,214,641
Messages
6,120,685
Members
448,977
Latest member
dbonilla0331

Share this page

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
Back
Top