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

 

[VoG]

Try like this. Repace

Range("B1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste

with

Range("B1").Copy Destination:=Sheets("Sheet2").Range(Rows.Count).End(xlUp).Offset(1)

 

[rhombus4]

Try like this. Repace

Range("B1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste

with

Range("B1").Copy Destination:=Sheets("Sheet2").Range(Rows.Count).End(xlUp).Offset(1)

created new macro and when I ran it got

Run Time Error 1004

Sub Macro2()
Range("B1").Copy Destination:=Sheets("Sheet2").Range(Rows.Count).End(xlUp).Offset(1)


End Sub

 

[rhombus4]

Try like this. Repace

Range("B1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A2").Select
    ActiveSheet.Paste

with

Range("B1").Copy Destination:=Sheets("Sheet2").Range(Rows.Count).End(xlUp).Offset(1)

[/QUOTE]Rather than copy them individually would it be easier just to copy the cells in B2:B4 and paste them further down sheet 1 in say A100:D100 then copy straight to Sheet 2 A2:D2 unless already occupied then copy to A3:D3 etc[/QUOTE]

 

[VoG]

Sorry

Range("B1").Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

 

[rhombus4]

Sorry

Range("B1").Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

Thanks Peter

 

[rhombus4]

Thanks Peter

If the data was to be transferred to a different spreadsheet where would I enter the spreadsheet location details

Range("A2:D2").Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

At the moment I have:-

Application.ScreenUpdating = False

NewName = ActiveWorkbook.Name

Workbooks.Open "C:\My Docs\Test.xls"

If ActiveWorkbook.ReadOnly = True Then
MsgBox "The database is being accessed by another user. Please wait.... If you receive any message regarding File Reservation, please choose 'Cancel'.", , "Save Record"
ActiveWindow.Close
Exit Sub
Else


'Data
Workbooks(NewName).Activate
Sheets("Sheet1").Activate
Range("A2:D2").Copy
Workbooks("Test.xls").Activate
Sheets("Sheet1").Activate
Range("A2").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


'Save and close YTD Spreadsheet

ActiveWorkbook.Save
ActiveWindow.Close

Workbooks(NewName).Activate
CutCopyMode = False
ThisWorkbook.Activate
Sheets("Sheet1").Select
Range("A1").Select


End If
MsgBox "Thankyou. The data you submitted has been saved sucessfully.", vbInformation, "Saved Data"

Application.ScreenUpdating = True

 

[VoG]

That would be like this

Range("A2:D2").Copy Destination:=Workbooks("Test.xls").Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

Please use code tags when posting code

[code]
your code here
[/code]

 

[rhombus4]

That would be like this

Range("A2:D2").Copy Destination:=Workbooks("Test.xls").Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)

Please use code tags when posting code

[code]
your code here
[/code]

Doesnt seem to copy anything

The Book where I run the macro from is just called Book1 and the destination is Test

 

[VoG]

Does this work?

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