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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
Hi
Thanks a lot, the problem disappeared, and the new problem is there is no
data in sheet2
 
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
 
Back
Top