Basic copy and paste macro

[jjlang22]

Hi, hopefully someone can help.

I have a 2 sheet spreadsheet.

The first sheet is an input sheet, with cells A2-5 all required entry fields.

The next sheet is a replica, but will hold all entries.

How do i write a macro which copys the data from sheet 1 into the next availabe blank row in sheet 2?
I have no experience of macro writing so would like to know all the steps from please.

Thanks

 

[jjlang22]

i made a mistake in my first post.

The input sheet has cells A1-E1 which need populated and the 2nd sheet replicates these

 

[VoG]

Hello and welcome to MrExcel.

Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert Menu and paste into the white space on the right.

Sub Cpy()
Sheets("Sheet1").Range("A1:E1").Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Press ALT + Q to close the code window, Tools > Macro > Macros, click on Cpy then click the Run button.

 

[jjlang22]

Hi VoG

Thanks for the reply, that's ideal.

Now I just need to work out how to delete the input sheet so it's blank for the next user, and I'm ready to go.

 

[VoG]

That should be straightforward:

Sub Cpy()
Sheets("Sheet1").Range("A1:E1").Cut Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[jjlang22]

Thanks for all your help on this.

One last question hopefully.

How do i change the code so I paste values?

I want to include a concatenate function in the input sheet but want the macro to copy and paste as a value in the 2nd sheet.

Thanks

 

[VoG]

Try

Sub Cpy()
With Sheets("Sheet1").Range("A1:E1")
    .Copy
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    .ClearContents
End With
End Sub

 

[jjlang]

that works now,

thanks