This eliminates the activation steps
between the workbooks. See if it runs
without throwing an error. Once again,
you will need to change the Workbooks(1)
and (2) and sheet number references to
meet your workbook name requirements.
Both workbooks must be open and the
first one opened will be Workbooks(1)
since the sytem assigns the index as the
files are opened.

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

" 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 -