Paste on next empty row

[MPFraser7]

Hello,

I'm currently using the following code to copy and paste a range of cells from one workbook to another. Everything is working as intended but next quarter, I need to run this macro again but have the quarter 2 results pasted on the next empty row. If I was to run it again the way it's currently set up, it would obviously overwrite quarter 1 results. How can I change my macro so that it doesn't overwrite what's already in there but rather paste starting on next empty row? Thanks!

Sub CommandButton1_Click()
Dim strfilename As String

For Each wb In Application.Workbooks
If wb.Name <> ActiveWorkbook.Name Then
strfilename = wb.Name
Debug.Print strfilename
End If
Next
Application.ScreenUpdating = False
Windows(strfilename).Activate
Sheets("Non Salary Spending").Select
Range("A13:N400").Select
Selection.Copy
Windows("PivotTool.xlsm").Activate
Sheets("NonSalaryByPeriod").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Joe4]

Try this:

Sub CommandButton1_Click()

    Dim strfilename As String
    Dim nextRow As Long

    For Each wb In Application.Workbooks
        If wb.Name <> ActiveWorkbook.Name Then
            strfilename = wb.Name
            Debug.Print strfilename
        End If
    Next
    
    Application.ScreenUpdating = False
    Windows(strfilename).Activate
    Sheets("Non Salary Spending").Select
    Range("A13:N400").Copy
    Windows("PivotTool.xlsm").Activate
    Sheets("NonSalaryByPeriod").Select
    [COLOR=#ff0000]nextRow = Cells(Rows.Count, "B").End(xlUp).Row + 1[/COLOR]
    Range("B" & [COLOR=#ff0000]nextRow[/COLOR]).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[healey21]

Adjust the line of code from:

Range("B2").Select

to:

Range("B65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select

Then the selection.Paste etc

 

[Joe4]

Range("B65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1).Select

All those extra Selects are not necessary.
You can combine all that into this:
Range("B65536").End(xlUp).Offset(1).Select

Note that the row 65536 is a holdover from the OLD Excel (xls) format that only allows 65536 rows. Newer versions of Excel (not so new really, 14 years old now!) allow for over a million.
So using Rows.Count will return however many rows that version of Excel has (so it is dynamic).
To see how to use it, see my code above in the first reply.