I tried several different configurations for the range in wb1 and wb2 and
cannot get the code to throw an error. It works perfect on my XP home ed
w/xl 2003.

"Tom Ogilvy" wrote:

Not sure what you tested, but if Workbooks(1) is active, then this line

Workbooks(2).Sheets(1).Activate

will certainly raise an error. And in your code, you do

Workbooks(1).Activate

I guess you tested with only one cell in A7:A10 being blank.

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
You need to have both workbooks open for the code to work. Also, you
should
use the Workbook names instead of (1) and (2) since Workbooks(1) is the
first
wb opened.

I ran it two or three different ways and don't get the error. Step
through
to make sure the counter reference is correct. It should start at 10 and
go
no further than 13 base of the four iterations of the range in wb 1.

" wrote:

Hi JLGWhiz,


for line:
Range("$G" & counter).Activate


It gives an error saying " Runtime 1004 : Activate method of Range
class failed"

Thank you for your help again.

On Jan 27, 7:42 pm, JLGWhiz wrote:
Try this:

Sub RwEmptCpy()
Set aRng = Workbooks(1).Worksheets(1).Range("$A$7:$A$10")
counter = 10
For Each c In aRng
If c = "" Then
xRng = c.Address
Workbooks(2).Sheets(1).Activate
Range("$G" & counter).Activate
If ActiveCell = "Day1" Then
ActiveCell.Copy Workbooks(1).Sheets(1).Range(xRng)
End If
counter = counter + 1
Workbooks(1).Activate
End If
Next
End Sub

It assumes that you are using Sheet 1 in both workbooks. If not you
will
need to change the sheet references.



" wrote:
SORRY ABOUT THE ERROR, PLEASE SEE THIS QUESTION:

I am looking for a formula function or a vba code whe

- In workbook1 find the first cell that is empty between range A7 -
A10,
- In workbook2, in Range G10- G13: find the word "Day1".
- If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy
the particular cell or cells where "Day1" exists to the first found
empty cell or cells in range A7-A10 in workbook1.

Lets suppose cells A8, A9, A10(workbook1) are empty cells, that means
A8 is the first empty cell.
And G10,G11,G12, G13 (workbook2) have the word "Day1"
Then,
Copy cell G10 into cell A8
Copy cell G11 into cell A9
Copy cell G12 into cell A10

Please let me know if this explanation is confusing.... and I can try
explaining again.

Appreciate all help!

On Jan 27, 6:34 pm, wrote:
I am looking for a formula function or a vba code whe

- In workbook1 find the first cell that is empty between range A7 -
A10,
- In workbook2, in Range G10- G13: find the word "Day1".
- If the word "Day1" exists in cells G10 or G11 or G12 or G13, copy
the particular cell or cells where "Day1" exists to the first found
empty cell or cells in range A7-A10 in workbook1.

Lets suppose cells A8, A9, A10(workbook1) are empty cells, that
means
A8 is the first empty cell.
And G10,G11,G12, G13 (workbook2) have the word "Day1"
Then,
Copy cell G10 into cell A8
Copy cell G13 into cell A9
Copy cell G14 into cell A10

Please let me know if this explanation is confusing.... and I can
try
explaining again.

Appreciate all help!- Hide quoted text -- Show quoted text -