Macro if already contains data

[rhombus4]

I have a macro in sheet1 which when pressed will transfer data into sheet 2. What I need it to do is look in row 2 to see if it contains anything and if blank then copy to row 2.

If row 2 is full then transfer to row 3 and so on.


In Sheet 1 I have the below in cells A1:A4 and I enter data in cells B2:B4

Name
Age
Nationality
Sex

In Sheet 2 the headings are across Row 1

NAme Age Nationality Sex

Not sure what to add to the macro so it will check to see if the row is empty or not
The Macro I currently have is :

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 29/06/2011 by browgins
'
'
Range("B1").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B3").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("D2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("B1").Select

End Sub

 

[rhombus4]

Does this work?

Workbooks(NewName).Sheets("Sheet1").Range("A2:D2").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A2").PasteSpecial Paste:=xlPasteValues

The above transfers the data correctly, although when I repeat with different data, it overwrites instead of pasting to the next available row

 

[VoG]

Try

Workbooks(NewName).Sheets("Sheet1").Range("A2:D2").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

 

[rhombus4]

Try

Workbooks(NewName).Sheets("Sheet1").Range("A2:D2").Copy
Workbooks("Test.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

thanks peter