Consolidate Sheets into Master Sheet

[Fisher1975]

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

 

[Fluff]

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

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.

 

[steve the fish]

Something like this perhaps. It may work or it may not because theres not enough info.

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

 

[Fisher1975]

Thank you very much, these helped a lot!

 

[Fluff]

Glad we could help & thanks for the feedback