Macro works in debug but not when assigned to a button

[PandaBear]

Hello,

I created this macro to move data over from 8 other tabs to 1 tab to create a report. It works when running it line by line via debug and when running the macro from the Developer ribbon. But when I assign it to a button it doesn't gives me the same result.

Sub CopyToReport()
    'Set shtCopyingSheet = ActiveSheet
    Set shtMainSheet = Sheets("Report")
    Set shtCopyingSheet = Sheets("SurveyReport")
    Dim varMainRow
    Dim varCopyingRow
    Dim lastRow As Long
    Dim aList(1 To 7) As String
    Dim item As Variant
      
    'Populate the array
    aList(1) = "Main Room"
    aList(2) = "Small Rooms"
    aList(3) = "Bathrooms"
    aList(4) = "Large Rooms"
    aList(5) = "Security Rooms"
    aList(6) = "Offices"
    aList(7) = "Issues"
    
    'Turn off calculations and screen updating to speed up process
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    
    'Start with SurveyReport to Report
    varCopyingRow = 9
    varMainRow = 9
        Do Until varCopyingRow > 400
            'Look at Column P for Yes, up to row 400
            If UCase(shtCopyingSheet.Range("P" & varCopyingRow)) = "YES" Then
                'If Yes then copy the row to other sheet
                shtMainSheet.Rows(varMainRow).Insert xlShiftDown
                shtCopyingSheet.Rows(varCopyingRow).Copy shtMainSheet.Rows(varMainRow)
                varMainRow = varMainRow + 1
            End If
        varCopyingRow = varCopyingRow + 1
        Loop
    
    'Run through the list
    For Each item In aList
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
        Set shtCopyingSheet = Sheets(item)
        varCopyingRow = 1
        varMainRow = lastRow
        Do Until varCopyingRow > 20
        'Look at Column P for Yes, up to row 400
        If UCase(shtCopyingSheet.Range("P" & varCopyingRow)) = "YES" Then
            'If Yes then copy the row to other sheet
            shtCopyingSheet.Rows(varCopyingRow).Copy shtMainSheet.Rows(varMainRow)
            varMainRow = varMainRow + 1
        End If
        varCopyingRow = varCopyingRow + 1
        Loop
    Next item
    
    'Clear
    shtMainSheet.Columns("P:AF").Delete
    
    'Set the print area
    shtMainSheet.PageSetup.PrintArea = shtMainSheet.Range("A1:O" & varMainRow - 1).Address
    
    'Turn calculations and screen updating back on
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    
    'Display Sheet
    shtMainSheet.Activate
  
End Sub

 

[BSALV]

when you debug or run it from the ribbon, is the activesheet the same is the one with the button ?
otherwise, the you have to specify the sheet for "lastrow"

For Each item In aList
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

If it' not that, what goes wrong ?

 

[PandaBear]

when you debug or run it from the ribbon, is the activesheet the same is the one with the button ?
otherwise, the you have to specify the sheet for "lastrow"

For Each item In aList
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1

If it' not that, what goes wrong ?

Oh thank you so much!!

Yes, that was the issue. Adding the sheet to the lastrow was what fixed it.