Macro to copy data and paste values into seperate sheet

Hi, I have a macro that copies some cells (which contain formulas) and pastes the values into a new line further down on the sheet. It works fine but I would like to have it paste the values into a separate sheet. I have tried various different code but can't get anything to work?

Hope someone can help?

Private Sub CommandButton1_Click()
Range("A1:D1").Select
    Selection.Copy
    Range("A100").Select
    ActiveCell.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Selection.EntireRow.Insert
 
   
End Sub

Assuming that you want to copy from sheet1 in to sheet 2. You need to change sheet names to reflect your actual sheet.

If you want to copy in to next empty column, use this line

ms.Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Private Sub CommandButton1_Click()
Dim ms As Worksheet
Set ms = Sheets("sheet2")

 With Sheets("sheet1")
    .Range("A1:D1").Copy
    ms.Range("A100").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
   .Range("A1:D1").EntireRow.Insert
 End With
   
End Sub

Hi Thanks, worked perfectly. I used the code to paste to next empty column. Also removed the entire row insert as that inserted a new row into sheet 1, thanks heaps.

Gordon