Combining Selected Sheets into one Master Sheet RRS feed

  • Question

  • I am sure this is quite a simple scenario, but I cannot work it out.  I have a Workbook with several sheets. I want to be able to select just two or three of those sheets and combine them into one master sheet. Lets call them - Stock Sheet 1, Stock Sheet 2 and Master Sheet. Stock Sheet 1 and 2 have exactly the same column headers, but the number of rows will increase and decrease from time to time. I want them to run as one long list in the Master Sheet.

    There are sheets in the document that I do not want to include on the Master Sheet.

    I have created this once before but cannot work out how I did it. I know I had a Macro button which I would need to hit to update the list. Thank you!
    Tuesday, May 1, 2018 4:22 AM

All replies

  • Hi,

    Could you provide your code in a Macro button?


    Ashidacchi --

    Tuesday, May 1, 2018 7:26 AM
  • This is the closest I have got so far but it is not working.  Can anyone see where I am going wrong?  This code should be taking all of the data and text from the two worksheets entitled Bethany SOH and Credaro SOH, and should be combining them on one long list in the sheet entitled DirectWholesaleSummary. 

    Sub CopyRangeFromMultiSheets()
        Dim sh As Worksheet
        Dim DestSh As Worksheet
        Dim Last As Long
        Dim CopyRng As Range

        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With

        Application.DisplayAlerts = False
        On Error Resume Next
        On Error GoTo 0
        Application.DisplayAlerts = True

        Set DestSh = ActiveWorkbook.Worksheets.Add
        DestSh.Name = "DirectWholesaleSummary"

        For Each sh In ActiveWorkbook.Worksheets
            If LCase(Left(sh.Name, 4)) = "Bethany SOH" Then
            If LCase(Left(sh.Name, 4)) = "Credaro SOH" Then
                Last = LastRow(DestSh)

                Set CopyRng = sh.Range("A1:H1")

                If Last + CopyRng.Rows.Count > DestSh.Rows.Count Then
                    MsgBox "There are not enough rows in the " & _
                       "summary worksheet to place the data."
                    GoTo ExitTheSub
                End If

                With DestSh.Cells(Last + 1, "A")
                    .PasteSpecial xlPasteValues
                    .PasteSpecial xlPasteFormats
                    Application.CutCopyMode = False
                End With


        Application.Goto DestSh.Cells(1)


        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub

    Tuesday, May 1, 2018 2:22 PM
  • Hi,

    At a first glance, there is a strange code:
    If LCase(Left(sh.Name, 4)) = "Bethany SOH" Then
        If LCase(Left(sh.Name, 4)) = "Credaro SOH" Then
    Strings "Bethany SOH" and "Credaro SOH" have characters more than 4.
    Those IF statement will never be True.

    If "4" is correct, code should be like this;
    If LCase(Left(sh.Name, 4)) = "Beth" Then
        If LCase(Left(sh.Name, 4)) = "Cred" Then
    And I cannot find "End If". Doesn't your code have syntax error? 


    Ashidacchi --

    Wednesday, May 2, 2018 12:31 AM
  • Hi ElishaRoxanne,

    Have you fixed this issue yet? If not please feel free to let us know the exact step block you.

    Regards & Fei

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Thursday, May 31, 2018 7:32 AM