Combine Multiple Worksheets Into One Summary Sheet.

[tandkb]

Hello,

I have 35 tabs that are updated daily. I want a summary worksheet that will combine everything that is on the 35 tabs. Info can be updated on these 35 tabs at anytime and would like the summary worksheet to reflect the changes made on any of the 35 tabs. Each tab has the exact same headers I just want the updated info to go into the summary sheet when someone fills it out on their tab. Does this make sense?

Thanks so much for your help!

 

[tandkb]

Thanks MAIT!

 

[My Aswer Is This]

So if a value in column A changes on any sheet in your workbook except for sheet named "Summary"
You must have a sheet named

Summary​

The row on that sheet will be added to the summary sheet.

Then try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu

In upper left corner of screen you should see:
Thisworkbook

Double click on Thisworkbook and Paste in the below code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  7/3/2019  2:48:36 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 1 And ActiveSheet.Name <> "Summary" Then
    Dim Lastrow As Long
    Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Summary").Rows(Lastrow)
End If
End Sub

 

[tandkb]

So I added it to the code but how will it run?

Shuld it just add it automaticly?

Thanks,

 

[My Aswer Is This]

When you change or add any value to column A of any sheet other then sheet named Summary the script will automatically run.

 

[tandkb]

Hmm then I must not of added it correctly.

 

[tandkb]

Ok I got it to work, the problem is that A is the first cell filled in and then they fill in B C D E F and G. A is the only cell that is moved to the summary tab.

However If they fill in B, C, D, E, F, G and then they fill in A it copies the entire row. Any idea how to fix this?

Thanks so much!!!

 

[My Aswer Is This]

That is why I asked how do you want the script activated.

So now this script will run when you change a value in Column 7 of any sheet.

If column 7 is not a good choice modify the script or provide more details how we want to activate the script.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  7/3/2019  4:20:37 PM  EDT
Dim c As Long
c = 7 'Modify 7 to what you want
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = c And ActiveSheet.Name <> "Summary" Then
    Dim Lastrow As Long
    Lastrow = Sheets("Summary").Cells(Rows.Count, c).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Summary").Rows(Lastrow)
End If
End Sub

 

[tandkb]

Perfect! Thanks so much for all your help! If I need to add tabs down the road will this still work or will I need to update the code?

 

[My Aswer Is This]

Yes it will work on any new sheets added down the road

I modified the script again.
So you will see the number 7 in two places in the script here marked in red
This represents column 7 so if you change your mine change the 7 in both places marked in Red.
This is the column that causes the script to run.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Modified  7/3/2019  4:53:31 PM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = [COLOR=#ff0000]7[/COLOR] And ActiveSheet.Name <> "Summary" Then
    Dim Lastrow As Long
    Lastrow = Sheets("Summary").Cells(Rows.Count, [COLOR=#ff0000]7[/COLOR]).End(xlUp).Row + 1
    Rows(Target.Row).Copy Sheets("Summary").Rows(Lastrow)
End If
End Sub