Merging specific worksheets

angix

New Member
Joined
Aug 23, 2019
Messages
5
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!


Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,004
Office Version
365
Platform
Windows
Hi & welcome to MrExcel.
How about
Code:
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

New Member
Joined
Aug 23, 2019
Messages
5
I think that worked - I feel like an idiot for not figuring this out sooner. Thank you much!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,004
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

angix

New Member
Joined
Aug 23, 2019
Messages
5
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,103
Office Version
2019
Platform
Windows
You should consider Power Query / Get and Transform. Any changes to the original data is automatically updated into the output.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
34,004
Office Version
365
Platform
Windows
How about
Code:
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

New Member
Joined
Aug 23, 2019
Messages
5
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".
 

Forum statistics

Threads
1,086,189
Messages
5,388,316
Members
402,114
Latest member
seanbrown01

Some videos you may like

This Week's Hot Topics

Top