Macro to copy data from multiple worksheets into 1 worksheet

[jillibillijames]

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.

 

[ColinKJ]

Hi jillibillijames

You may want yo take a look at this post, very similar yours

http://www.mrexcel.com/forum/showthread.php?t=534040

 

[jillibillijames]

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

 

[ColinKJ]

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.

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.

 

[jillibillijames]

Thank u so much... It worked...

Regards
James