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

[camccull]

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

 

[JLGWhiz]

Try this

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

 

[camccull]

Sorry it's taken me so long to respond, but thank you so much! This macro worked perfectly!