Hi Igor
Create a new Sheet in your file called All Data.
Copy the Header row from one of your other sheets to this Sheet.
Amend the Macro below, to set what is the last column of data on each sheet,
and the column next to this which is where an entry will be created for the
name of the source sheet against each row of data.
Run the Macro below, which will combine data from as many sheets as there
are in your file, which do not contain Report or Data in their name.
You will have a consolidated set of data for all 10 sheets that you can then
use to create a Pivot Table, the Source column showing where the data came
from.
Rename the Sheet where your PT is created to something including the word
Report, so it will not be included in the Consolidation next time you run
it.
Sub CombineSheets()
Dim Sht As Worksheet, SummarySht As Worksheet
Dim NewRow As Long, LastRow As Long
Const Lastcol = "Z" 'Set for last column of data
Const SourceCol = "AA" ' next column to above
Application.ScreenUpdating = False
NewRow = 2
Set SummarySht = Sheets("All Data")
SummarySht.range("2:65536").Delete
For Each Sht In ThisWorkbook.Sheets
'Check it is not a Report or Data Sheet
If InStr(Sht.Name, "Report") = 0 _
And InStr(Sht.Name, "Data") = 0 Then
LastRow = Sht.range("A" & Rows.Count).End(xlUp).Row
If NewRow + LastRow > 65536 Then
MsgBox "Cannot consolidate all data " _
& "as there are too many rows"
GoTo Endsub
End If
Sht.range("A2:" & Lastcol & LastRow).Copy _
SummarySht.range("A" & NewRow)
SummarySht.range(SourceCol & NewRow & ":" _
& SourceCol & LastRow + NewRow - 1) = Sht.Name
NewRow = NewRow + LastRow - 1
End If
Next Sht
Endsub:
With SummarySht
Columns("A:" & SourceCol).EntireColumn.AutoFit
range(SourceCol & "1") = "Source"
Rows("1:1").RowHeight = 35
Rows("1:1").VerticalAlignment = xlTop
range("A2").Select
ActiveWindow.FreezePanes = True
Application.ScreenUpdating = True
End With
End Sub
Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel
To use
Alt+F8 to bring up Macros
Highlight the macro name
Run
