Merging specific worksheets

angix

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

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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
 
Upvote 0
I think that worked - I feel like an idiot for not figuring this out sooner. Thank you much!
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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 :)
 
Upvote 0
You should consider Power Query / Get and Transform. Any changes to the original data is automatically updated into the output.
 
Upvote 0
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
 
Upvote 0
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".
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top