Merging specific worksheets

[angix]

So, I have a code that combines ALL worksheets and puts them in a combined worksheet. I just want to combine specific worksheets called 'US Data' and 'Canada Data' and not combine the rest of the worksheets I need to add - this is the code I have, do I need a new code or.... ? Any help would be excellent. Thank you!

Sub Combine()'Update
    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", , "x"
        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

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Combine() 'Update
    Dim i As Integer
    Dim xTCount As Variant
    Dim xWs As Worksheet, Ws 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", , "x"
        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 Each Ws In Sheets(Array("US Data", "Canada Data"))
        Ws.Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
               Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
    Next Ws
End Sub

 

[angix]

I think that worked - I feel like an idiot for not figuring this out sooner. Thank you much!

 

[Fluff]

You're welcome & thanks for the feedback

 

[angix]

Is there a way to get this macro to overwrite the same sheet when it combines? I tried adjusting the worksheet name, but it's still creating separate sheets. I just want it to overwrite the "Combined" sheet. Is there a way to do this?

Thank you again for your help, it's super appreciated

 

[alansidman]

You should consider Power Query / Get and Transform. Any changes to the original data is automatically updated into the output.

 

[Fluff]

How about

Sub Combine() 'Update
    Dim i As Integer
    Dim xTCount As Variant
    Dim xWs As Worksheet, Ws 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", , "x"
        GoTo LInput
    End If
    
    Set xWs = Sheets("Combined")
    xWs.Rows("2:" & Rows.Count).ClearContents
    For Each Ws In Sheets(Array("US Data", "Canada Data"))
        Ws.Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
               Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
    Next Ws
End Sub

 

[angix]

You should consider Power Query / Get and Transform. Any changes to the original data is automatically updated into the output.

My end users have a terrible time with power query since it appears everyone is using different versions of excel and we get errors when they try to refresh data themselves. I just want it to be "click this button and it will do what you want".