Results 1 to 8 of 8

Thread: Merging specific worksheets

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Merging specific worksheets

    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

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,403
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Merging specific worksheets

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging specific worksheets

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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,403
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Merging specific worksheets

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging specific worksheets

    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

  6. #6
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,944
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging specific worksheets

    You should consider Power Query / Get and Transform. Any changes to the original data is automatically updated into the output.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,403
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Merging specific worksheets

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Merging specific worksheets

    Quote Originally Posted by alansidman View Post
    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".

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •