Macro Code - Consolidate Data from Multiple Sheets into One Master Data Sheet (Please Help!)

camccull

New Member
Joined
Apr 8, 2016
Messages
11
Hello,

I have a workbook that contains 200 tabs. Each tab has 4 columns (A:E) w/ same header measures, but each tab has varying numbers of rows. The data for each tab starts in cell A2. I would like to create a macro that will select & copy all the data from cell A2 to the end of the data range (which will change for each tab), and then paste the data underneath the last row in my Master worksheet. I want to make sure no data is overlapping. Please help!

Thanks,
Claire
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Try this
Code:
Sub consold()
Dim sh As Worksheet, lr As Long
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Master" Then ' If your master sheet is not named Master then change this.
            lr = sh.Cells.Find("*", , xlFormulas, xlPart, xlByRows, xlPrevious).Row
            sh.Range("A2:E" & lr).Copy Sheets("Master").Cells(Rows.Count, 1).End(xlUp)(2) 'Edit Master name
        End If
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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