Creating Summary Tab from All Worksheets

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
82
Hello, I am looking to create a macro that will create a "Summary" tab within my workbook that will copy a defined range (A7:B56) from all other worksheets and paste them to a new summary tab. I would like it to copy the formatting as well as the value. Also, I'm hoping to include the original worksheet name where the data came from as a column in the summary as well. A brief example below of how the summary tab would look where my two tab names are Animals and Movies (except less rows than my actual data). Then finally, if possible, I would like to delete any rows that had "None" in column A originally. Thanks for any help!

1621342432416.png
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,233
Office Version
  1. 2013
Platform
  1. Windows
Your image shows the sheet name in a column before column A
There is no column before column A
This script puts the sheet name in column A and the other data in column B and C

Try this:
VBA Code:
Sub Create_Summary_Sheet()
'Modified  5/19/2021  1:22:42 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets.Add(Before:=Sheets(1)).Name = "Summary"

For i = 2 To Sheets.Count
    Lastrow = Sheets("Summary").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Sheets(i).Range("A7: B56").Copy Sheets("Summary").Cells(Lastrow, 2)
    Sheets("Summary").Cells(Lastrow, 1).Resize(50).Value = Sheets(i).Name
Next
Application.ScreenUpdating = True
End Sub
 
Solution

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
82
Your image shows the sheet name in a column before column A
There is no column before column A
This script puts the sheet name in column A and the other data in column B and C

Try this:
VBA Code:
Sub Create_Summary_Sheet()
'Modified  5/19/2021  1:22:42 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets.Add(Before:=Sheets(1)).Name = "Summary"

For i = 2 To Sheets.Count
    Lastrow = Sheets("Summary").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Sheets(i).Range("A7: B56").Copy Sheets("Summary").Cells(Lastrow, 2)
    Sheets("Summary").Cells(Lastrow, 1).Resize(50).Value = Sheets(i).Name
Next
Application.ScreenUpdating = True
End Sub
Sorry for the confusion on the columns. What I was trying to show was that they were columns A and B from the rest of the worksheets in the book, but would be B and C in the summary, which is how you set it up anyways. Everything appears to be working great. I appreciate your help!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
18,233
Office Version
  1. 2013
Platform
  1. Windows
Sorry for the confusion on the columns. What I was trying to show was that they were columns A and B from the rest of the worksheets in the book, but would be B and C in the summary, which is how you set it up anyways. Everything appears to be working great. I appreciate your help!
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,572
Messages
5,770,923
Members
425,652
Latest member
Pemby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top