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

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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
--
Gary''s Student - gsnu200738


"Jon" wrote:

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

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


"Gary''s Student" wrote:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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
--
Gary''s Student - gsnu200738


"Jon" wrote:

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

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.
--
Gary''s Student - gsnu200738


"Jon" wrote:

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


"Gary''s Student" wrote:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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
--
Gary''s Student - gsnu200738


"Jon" wrote:

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

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


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200738


"Jon" wrote:

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


"Gary''s Student" wrote:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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
--
Gary''s Student - gsnu200738


"Jon" wrote:

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

FIRST put data in Sheet1 the first row cells A1 thru D1.
THEN run the macro.
--
Gary''s Student - gsnu200738


"Jon" wrote:

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


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200738


"Jon" wrote:

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


"Gary''s Student" wrote:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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
--
Gary''s Student - gsnu200738


"Jon" wrote:

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

This is a little more precise:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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


--
Gary''s Student - gsnu200738


"Jon" wrote:

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


"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200738


"Jon" wrote:

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


"Gary''s Student" wrote:

Sub xfr_data()
Dim r As Range
Set r1 = Sheets("Sheet1").Range("A1:D1")
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
--
Gary''s Student - gsnu200738


"Jon" wrote:

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