Creating Summary Tab from All Worksheets

[cheesiepoof05]

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!

 

[My Aswer Is This]

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:

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

 

[cheesiepoof05]

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:

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]

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.