How to consolidate identical data from different sheets?

[CopperKnife]

Hi,

I have a workbook with 365 worksheets with the following Column headers. In every worksheet data starts from first row to 85 row only. It is all identical in all the sheets. I want to consolidate into one single sheet. But it spread over multiple sheets when I copied a PDF and converted into Excel file. I have seen people use INDIRECT function for this problem, but no clear idea how to use it for this. Can we do it using VBA code? Please help me. Advance thanks.

SNo--RollNo--Name--DOB--Place--Centre

CopperKnife

 

[Michael M]

If you simply want all sheets combined....create a sheet called "Master", then run this code

Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.UsedRange.Copy
    Sheets("Master").Cells.SpecialCells(11).Offset(1, 0).End(xlToLeft).PasteSpecial xlValues
Next ws
End Sub

 

[CopperKnife]

Hi Michael,

Without looking at the data you have written this code like a gun, Thank you so much.
CopperKnife

 

[Trebor76]

Can I ask why you have a separate tab for each day of the year? Surely it would be easier to have everything in one tab with an extra column for date that you could simply filter on?

 

[Michael M]

@Robert
I think it's downloaded data from an external source

But it spread over multiple sheets when I copied a PDF and converted into Excel file.

 

[Trebor76]

Ah - OK then. Thanks Michael