Consolidate Sheets into Master Sheet

Fisher1975

Board Regular
Joined
Jan 12, 2012
Messages
74
Hi

Im looking for a bit of help with a macro.

I have a workbook with multiple tabs of data, all in the same format that I want to consolidate to a master tab.

The tabs to be consolidated are between 2 tabs named Site>>> and <<<Site

Data starts at E11 and ends at CE200
<site

Anyone know a quick macro to do this?

Thanks in advance</site
 
Last edited:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
This will copy data from various sheets into a sheet called Master, with the data being copied to the first blank row in col E
Code:
Sub ConsolidateSheets()

    Dim Cnt As Long

    For Cnt = Sheets("[COLOR=#ff0000]Site[/COLOR]").Index + 1 To Sheets("[COLOR=#ff0000]End[/COLOR]").Index - 1
        Sheets(Cnt).Range("E11:CE200").Copy _
            Sheets("[COLOR=#ff0000]Master[/COLOR]").[COLOR=#0000ff]Range("E" & Rows.Count).End(xlUp).Offset(1)[/COLOR]
    Next Cnt

End Sub
As you only gave us the name of 1 sheet, you'll probably have to change the sheet names in red to suit.
Also as you did not say what range to copy to, you may need to change the part in blue.
 
Upvote 0
Something like this perhaps. It may work or it may not because theres not enough info.

Code:
For Each sh In ThisWorkbook.Worksheets
    If sh.Name = "Site" Then site = sh.Index
    If sh.Name = "Data" Then data = sh.Index
Next

For i = Application.Min(site, data) + 1 To Application.Max(site, data) - 1
    lr = Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row + 1
    Range(Sheets("Master").Cells(lr, "A"), Sheets("Master").Cells(lr + 189, "CA")).Value = Sheets(i).Range("E11:CE200").Value
Next
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

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