Run Macro to Consolidate Multiple Tabs to One with same format with tab name added as column

kelbrod

New Member
Joined
Jul 16, 2014
Messages
11
I need to consolidate about 50 tabs to one master. All tabs have the same format A-G. I would like the master tab to include the name of the tab in a new column "H" so I know which data came from which tab. I tried running a macro I found online and am getting an error "Compile Error: Sub or Function not defined". I oould not find how to attach a file so cannot share the macro yet.

This is a Excel enabled macro workbook (xlsm) and I've run other macros without fail so not sure why I am getting this error?

Thank you.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,095
This macro assumes there are headers in A1:G1 of all the sheets to be copied and the "Master" sheet has headers in A1:H1. Make sure you have a sheet named "Master".
Code:
Sub consolidateSheets()
    Application.ScreenUpdating = False
    Dim LastRow As Long, bottomH As Long, ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("[COLOR="#FF0000"]Master[/COLOR]")
    For Each ws In Sheets
        If ws.Name <> "Master" Then
            ws.UsedRange.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
            LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            bottomH = desWS.Range("H" & desWS.Rows.Count).End(xlUp).Row
            desWS.Range("H" & bottomH + 1 & ":H" & LastRow) = ws.Name
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,745
Members
413,929
Latest member
Hypatia

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