Combining all worksheets but one in same workbook

[rachel06]

Hello,

This one should be pretty easy. I want to combine all of the sheets in my workbook into one minus the "Dashboard" sheet. I found this code that works for the most part, I just haven't been able to get the code to ignore the "Dashboard" sheet.


Sub CombineAP35()

Dim i As Integer
Dim xTCount As Variant
Dim xWs As Worksheet
On Error Resume Next
LInput:
xTCount = Application.InputBox("The number of title rows", "", "1")
If TypeName(xTCount) = "Boolean" Then Exit Sub
If Not IsNumeric(xTCount) Then
MsgBox "Only can enter number"
GoTo LInput
End If
Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
xWs.Name = "Combined"
Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
For i = 2 To Worksheets.Count
Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
Next
End Sub

 

[Trebor76]

Hi Rachel,

Try this:

Option Explicit
Sub CombineAP35()

    Dim i As Integer
    Dim xTCount As Variant
    Dim xWs As Worksheet

    On Error Resume Next
    
LInput:
    xTCount = Application.InputBox("The number of title rows", "", "1")
    If TypeName(xTCount) = "Boolean" Then Exit Sub
    If Not IsNumeric(xTCount) Then
        MsgBox "Only can enter number"
        GoTo LInput
    End If

    Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
    xWs.Name = "Combined"
    Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
    For i = 2 To Worksheets.Count
        If StrConv(Worksheets(i).Name, vbProperCase) <> "Dashboard" Then
            Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
            Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
        End If
    Next i
    
End Sub

Note the way it currently stands the code will only consolidate the data if there's no sheet called "Combined" in the workbook to start with.

Regards,

Robert

 

[alansidman]

another option is Power Query

Consolidate Multiple Excel Sheets Using Power Query Append

Power Query Amend in Excel is an easy way to consolidate multiple Excel worksheets. Click here for the free tutorial and start learning!

www.myexcelonline.com

 

[rachel06]

PERFECT!! Thanks a bunch!!

 

[rachel06]

another option is Power Query

Consolidate Multiple Excel Sheets Using Power Query Append

Power Query Amend in Excel is an easy way to consolidate multiple Excel worksheets. Click here for the free tutorial and start learning!

www.myexcelonline.com

I always forget about Power Query! I use that with Power BI on my personal computer, but I don't know that I have access to that at work. I like to make things really simple for my colleagues at work - clicking a button and having it do its thing. I appreciate the reminder about PowerQuery though! I have to start using that more.

 

[Trebor76]

PERFECT!! Thanks a bunch!!

You're welcome

 

[alansidman]

Rachel,
If you are running 365 at work then it is called Get and Transform. It is native in 2016 and 2019 also. Needs to be added in if using 2010 or 2013.

 

[rachel06]

Rachel,
If you are running 365 at work then it is called Get and Transform. It is native in 2016 and 2019 also. Needs to be added in if using 2010 or 2013.

I appreciate the tip! Thank you!!