Creating Master Summary from multiple sheets

Hi All!

I am trying to create a master summary sheet (for the entire year) from mutiple sheets (each sheet being a month). I would like the master to update automtacially when changes are made in each monthly tab. I have attached a sample document and am currently using the following VBA coding


Sub Summary()
Dim i As Long
Dim lr As Long
Dim ws As Worksheet
Dim x As String


Sheets.Add.Name = "Master Sheet 2013"

x = Array("July", "Aug", "Sept", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun")

For i = LBound(x) To UBound(x)

lr = Cells(Rows.Count, 1).End(3).Row

Range(Range("A"), Range("A" & lr)).EntireRow.Copy Sheets("Master Sheet 2013").Range("A" & Rows.Count).End(xlUp)(2)

Next i


End Sub


I am clearly missing something and ANY help would be appreciated! Thank you!

Thank you for your help! Is there a way to make the master automatically update when changes and/or additional rows are added to the monthly sheets?

Thank you all so much! It worked perfectly! I really appreaciate all the help

Originally Posted by krosser

Thank you all so much! It worked perfectly! I really appreaciate all the help

Private Sub Worksheet_Activate()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim FileName As String
Dim n As Long
Dim SourceRange As Range
Dim DestRange As Range
Dim WB As Workbooks
FolderPath = "C:\Users\aruna\Desktop\Oops\"
FileName = Dir(FolderPath & "*.xls*")
Set WorkBk = Workbooks.Open(FolderPath & FileName)
SummarySheet.Range("A" & NRow).Value = FileName
Set SourceRange = WorkBk.Worksheets(1).Range("A9:bz9")
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
ActiveSheet.UsedRange.EntireRow.Delete
Set SourceRange = WorkBk.Worksheets(1).UsedRange.EntireRow.Delete
x = Array("July", "Aug", "Sept", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar", "Apr", "May", "Jun")
For i = LBound(x) To UBound(x)
lr = Cells(Rows.Count, 1).End(3).Row
Sheets(x(i)).UsedRange.Copy Sheets("Master Sheet 2013").Range("A" & Rows.Count).End(xlUp)(2)
Next i
WorkBk.Close savechanges:=False
End Sub



Dude i want pass the code through multiple closed workbooks i almost done but there some error

i got struck here..please help me out urgent

every workbook in path range copy to Master Sheet 2013


SummarySheet.Range("A" & nRow).Value = FileName