Consolidate all data from number of sheets into another sheet within a workbook

Hi,

I have an excel file that has 5 sheets named
"001"
"002"
"003"
"004"
"ALL"

All the sheets have exactly the same headers with different data.

I want to write a vba code such that the data from 4 sheets (001,002,003,004) will be copied into the sheet named "All" such that in sheet "All" I will have the same headers with a consolidated data from 001,002,003 and 005.

Note that a sheet might not have a data and header at all, but there will definteley be at least one sheet containing the data above.

See attached sample file.


Is this possible?



Thank you.

Hello Lilit2222,

All the sheets have exactly the same headers...........

Note that a sheet might not have a data and header at all

Isn't this contradictory?

For proper use and manipulation of data sets, Excel requires uniformity. Please ensure that all sheets at least have headings, including the "ALL" sheet then try the following code assigned to a button:-

Sub Test()

Dim ws As Worksheet

Application.ScreenUpdating = False
        
        For Each ws In Worksheets
                If ws.Name <> "ALL" Then
                ws.UsedRange.Offset(1).Copy Sheet5.Range("A" & Rows.Count).End(3)(2)
                End If
        Next ws

Application.ScreenUpdating = True

End Sub

The code assumes that you are consolidating all data from all worksheets into the "ALL" worksheet.

I hope that this helps.

Cheerio,
vcoolio.

Thank you. I have made amendment such that all sheets including "ALL" have the same headers.

However, when I try to run the code it brings "run time error 424 Object required" and the line starting with ws.UsedRange is highlighted.

Do you have any suggestions?

Many thanks,

How can I check whether the sheet code of "ALL" is Sheet5?

Thank you this has been resolved

Hello Lilit2222,

Could you please post back and let us know how it was resolved. This will finalise this thread and will benefit anyone else who may come by this thread looking for a similar solution.

Thank you.

Cheerio,
vcoolio.