how to transfer data from sheet to sheet with new range?

[Guest]

Greeting
I have a work book with two sheets. Sheet 1 is for entering data with range
from a1to d1. in sheet 2, I need the range in sheet 1 to be transferred to
a1:d1. What I want to do is when entering data in sheet 1, the data transfer
to sheet 2 and if there is a data in the above rang, a new range is setting
for the new data.
thanks

 

[Guest]

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A11")
For i = 1 To Rows.Count
n = Application.WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, 4)))
If n = 0 Then
Set r2 = Sheets("Sheet2").Cells(i, 1)
r1.Copy r2
Exit Sub
End If
Next
End Sub

Run the macro after you have entered data in Sheet1

 

[Guest]

hi,
there is an error"error 9
subscript out of range"
Please advice?

 

[Guest]

Most likely, the spelling of the worksheet names. This code assumed:

Sheet1
Sheet2

If your tab names are different, just change the code to match the names.

 

[Guest]

Hi
Thanks a lot, the problem disappeared, and the new problem is there is no
data in sheet2

 

[Guest]

FIRST put data in Sheet1 the first row cells A1 thru D1.
THEN run the macro.

 

[Guest]

This is a little more precise:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A11")
Sheets("Sheet2").Activate
For i = 1 To Rows.Count
n = Application.WorksheetFunction.CountA(Range(Cells(i, 1), Cells(i, 4)))
If n = 0 Then
Set r2 = Sheets("Sheet2").Cells(i, 1)
r1.Copy r2
Exit Sub
End If
Next
End Sub