Adding "1" to row when copying data range from one sheet to another

[allstarsosa]

Currently I have a pretty basic commandbutton that copies a range of data from my "import" sheet to a row in my "Main" sheet. What I am trying to now do is have the row on my "Main" sheet increase by 1 each time I click this button. So the first time I press this button the data will copy and paste to A10 on "Main", then next time i press the button it will paste to A11 on "Main" and so on, A12, A13, Etc... Whether this is even possible im not sure.

Private Sub CommandButton1_Click()  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet


  Set copySheet = Worksheets("Import")
  Set pasteSheet = Worksheets("Main")


  copySheet.Range("E4:AA4").Copy
  pasteSheet.Range("A10").PasteSpecial xlPasteValues
  Application.CutCopyMode = False
  Application.ScreenUpdating = True


End Sub

 

[Trebor76]

Hi allstarsosa,

Welcome to MrExcel!!

Try this:

Option Explicit
Sub Macro1()
    
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim lngPasteRow As Long
    
    Application.ScreenUpdating = False

    Set copySheet = Worksheets("Import")
    Set pasteSheet = Worksheets("Main")
    
    lngPasteRow = pasteSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
    If lngPasteRow < 10 Then
        lngPasteRow = 10
    End If

    copySheet.Range("E4:AA4").Copy
    pasteSheet.Range("A" & lngPasteRow).PasteSpecial xlPasteValues
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[allstarsosa]

Appears to be working as I need. Thank you very much!

 

[Trebor76]

You're welcome and thanks for the thanks