Combining Sheets for Greater Than Number not Specific Range or using Sheet Name

amitaj

New Member
Joined
Nov 18, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
I've see several posts with vba codes to combine all sheets in a workbook into one sheet or select certain sheets based on sheet name or by identifying sheets x to y, but cannot find how to combine sheets from sheet 7 and greater.

I want to combine all sheets in my file to a "Summary" starting with sheet 7 and the number of sheets beyond 7 can vary it could be 4 additional sheets or 20 additional sheets. The sheet names vary each time so I cannot use that.

This is what I have currently which specifies I want to combine data from Sheet7 to Sheet9. If I only have up until sheet 9 in my file and if I try to overachieve in the code to say 7 to 20, the data from sheet 9 will duplicate the additional 11 times which is why this won't work.

Sub Summary()
Dim J As Integer
Dim s As Worksheet

' Add a sheet in first place
On Error Resume Next
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Summary"

' Copy headings
Sheets(7).Activate
Range("A1").EntireRow.Select
Selection.Copy Destination:=Sheets(1).Range("A1")

'This is to combine the range of identified worksheets
Dim Cnt
For Cnt = 7 To 9
Application.Goto Worksheets.Item(Cnt).[A1]
Selection.CurrentRegion.Select
' Don't copy the headings
Selection.Offset(2, 0).Resize(Selection.Rows.Count - 1).Select
Selection.Copy Destination:=Sheets("Summary"). _
Cells(Rows.Count, 1).End(xlUp)(2)
Next Cnt
End Sub

Hoping someone can help!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,120
Office Version
  1. 365
Platform
  1. Windows
If I only have up until sheet 9 in my file and if I try to overachieve in the code to say 7 to 20, the data from sheet 9 will duplicate the additional 11 times which is why this won't work.
Sometimes it's appropriate to use On Error Resume Next where an error may occur but your code expects and allows for this.

The way you are using it is dangerous - your code may error anywhere but will continue, possibly producing incorrect or unintended results. Your code is erroring every time you try to go to a worksheet that doesn't exist, and each time it will copy the last sheet.

Try this:

VBA Code:
Sub Summary()
    
    Dim Summary As Worksheet
    Dim i As Long, counter As Long
    Const START_SHEET = 7, HEADER_ROWS = 2
    
    Set Summary = Worksheets.Add(Before:=Worksheets(1))
    Summary.Name = "Summary"
    
    For i = START_SHEET To Worksheets.Count
        With Worksheets(i).Range("A1").CurrentRegion
            If .Rows.Count <= HEADER_ROWS Then
                MsgBox "No data in worksheet " & .Parent.Name, vbOKOnly + vbExclamation
            Else
                .Offset(HEADER_ROWS).Resize(.Rows.Count - HEADER_ROWS).Copy Summary.Cells(counter + 1, 1)
                counter = counter + .Rows.Count - HEADER_ROWS
            End If
        End With
    Next i
        
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,584
Messages
5,625,644
Members
416,124
Latest member
DeMoNloK

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