Copy Paste VBA Macro

[naturegal27]

Help!

I have a macro on a button to copy and paste a range into a new worksheet. I need it to add a new line so that every time a user click, a new line of data will be added. Help!

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

Set copySheet = Worksheets("Final")
Set pasteSheet = Worksheets("Raw Data")

copySheet.Range("Raw").Copy
pasteSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[Beyond_avarice]

Private Sub CommandButton1_Click()

 Application.ScreenUpdating = False

Dim lRow As Long
            lRow = Sheets("Raw Data").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

            Set r = ActiveWorkbook.Names("Raw").RefersToRange

 Sheets("Raw Data").Range("A" & lRow & ":" & Cells(lRow + r.Rows.Count - 1, r.Columns.Count).Address).Value = Sheets("Final").Range("Raw").Value

Application.ScreenUpdating = True

 End Sub

 

[naturegal27]

Thanks but I still can't get it to add to the next line. It just replaces the same line on "Raw Data".

 

[Fluff]

Wrong

 

[Fluff]

Alternatively, if you have formulas in the original range try

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
    Dim copySheet As Worksheet
    Dim pasteSheet As Worksheet
    Dim NxtRow As Long
    Dim Rng As Range
    
    Set copySheet = Worksheets("Data")
    Set pasteSheet = Worksheets("Sheet3")
    Set Rng = Range("Raw")
    
    With pasteSheet
        NxtRow = .Cells.Find("*", After:=.Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Row
        .Range("A" & NxtRow).Resize(Rng.Rows.Count, Rng.Columns.Count).Value = Rng.Value
    End With
    
Application.ScreenUpdating = True
End Sub