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!

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!

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!

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 -

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 -

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 -

Nope, I tested with all cells blank in wb 1 A7 - A10 and with diferent cells
in wb 2 G10 - G13 = "Day1". I did get an error when I first wrote it with
the range included, but when I put the Range activate on a separate line, the
error went, away.

So rachitm, try it this way;
Delete this line: Workbooks(2).Sheets(1).Activate
Add two lines and end up with this:

Workbooks(2).Activate
Worksheets(1).Activate
Range("$G" & counter).Activate

If that don't work, I'm out of suggestions.


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

Tom, Workbooks(1) has to be activated for each iteration. The code starts in
with wb1 being the active wb. If the range in wb 1 is empty it activates wb2
to check the range there for data, if found, it goes back to wb 1 to check
the next cell in the wb1 range. Would it check that range if I don't
activate it??? or would it just check that range in wb2 which would be an
invalid query?? Help me out here.

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

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 -

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 -

Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1




On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -

See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub


" wrote:


Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1




On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -

Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.


On Jan 28, 2:01 pm, JLGWhiz wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub



" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

That would take a different kind of code to search for part of the total data
field instead of an exact match. It could probably be done with the Find
method, but I have not tried a lot of that type coding. I think I have done
it before, but it was some time ago before I retired. I sort of stick with
the simple stuff nowadays.

" wrote:

Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.


On Jan 28, 2:01 pm, JLGWhiz wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub



" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

However, If you use this line:

If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

Instead of this one:

If Workbooks(2).Sheets(1).Range("$G" & counter) = "Day1" Then

It might do what you are asking.

" wrote:

Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.


On Jan 28, 2:01 pm, JLGWhiz wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub



" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

Thanks again JLG, Thank you for all your help. I had already tried
doing"*Day1*" before, but it didnt work as expected.


On Jan 28, 6:44 pm, JLGWhiz wrote:
However, If you use this line:

If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

Instead of this one:

If Workbooks(2).Sheets(1).Range("$G" & counter) = "Day1" Then

It might do what you are asking.



" wrote:
Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.

On Jan 28, 2:01 pm, JLGWhiz wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub

" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

Using the operator "Like" instead of "=" gave me a return of the entire
sentence. If you only wanted to return "Day1" out of the sentence, it would
probably have to be some kind of a Len(Trim( ) combination and I a not that
adept with those functions.

" wrote:

Thanks again JLG, Thank you for all your help. I had already tried
doing"*Day1*" before, but it didnt work as expected.


On Jan 28, 6:44 pm, JLGWhiz wrote:
However, If you use this line:

If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

Instead of this one:

If Workbooks(2).Sheets(1).Range("$G" & counter) = "Day1" Then

It might do what you are asking.



" wrote:
Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.

On Jan 28, 2:01 pm, JLGWhiz wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub

" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly the
same thing it is suppossed to. It doesnt copy the information to the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz wrote:
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 -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

If you only want to return Day1

Within your conditional code:
If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

then instead of doing

Activesheet.Paste

just do

ActiveCell.Value = "Day1"

Again, assuming only returning the value "Day1"
the whole thing could probably be done with countif to count the number of
cells in the source range containing Day1,

then use special cells in the destination to get the blank cells and fill
the subset of those cells for the count returned by countif with Day1

But I doubt the OP just wants Day1.

--
Regards,
Tom Ogilvy




"JLGWhiz" wrote in message
...
Using the operator "Like" instead of "=" gave me a return of the entire
sentence. If you only wanted to return "Day1" out of the sentence, it
would
probably have to be some kind of a Len(Trim( ) combination and I a not
that
adept with those functions.

" wrote:

Thanks again JLG, Thank you for all your help. I had already tried
doing"*Day1*" before, but it didnt work as expected.


On Jan 28, 6:44 pm, JLGWhiz wrote:
However, If you use this line:

If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

Instead of this one:

If Workbooks(2).Sheets(1).Range("$G" & counter) = "Day1" Then

It might do what you are asking.



" wrote:
Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.

On Jan 28, 2:01 pm, JLGWhiz
wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub

" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly
the
same thing it is suppossed to. It doesnt copy the information to
the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz
wrote:
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 -- Hide quoted text -- Show quoted text -- Hide
quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -

Thanks, Tom. We got the first one for just the Day1 entry. The this
question was posed:

question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

It seemed to me that the Like operator with wildcards would work, and it did
return the sentence "This is Day1 of many" for me in a test, but the OP said
they didn't get the desired result. Since there was no further explanation,
I give up.


"Tom Ogilvy" wrote:

If you only want to return Day1

Within your conditional code:
If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

then instead of doing

Activesheet.Paste

just do

ActiveCell.Value = "Day1"

Again, assuming only returning the value "Day1"
the whole thing could probably be done with countif to count the number of
cells in the source range containing Day1,

then use special cells in the destination to get the blank cells and fill
the subset of those cells for the count returned by countif with Day1

But I doubt the OP just wants Day1.

--
Regards,
Tom Ogilvy




"JLGWhiz" wrote in message
...
Using the operator "Like" instead of "=" gave me a return of the entire
sentence. If you only wanted to return "Day1" out of the sentence, it
would
probably have to be some kind of a Len(Trim( ) combination and I a not
that
adept with those functions.

" wrote:

Thanks again JLG, Thank you for all your help. I had already tried
doing"*Day1*" before, but it didnt work as expected.


On Jan 28, 6:44 pm, JLGWhiz wrote:
However, If you use this line:

If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

Instead of this one:

If Workbooks(2).Sheets(1).Range("$G" & counter) = "Day1" Then

It might do what you are asking.



" wrote:
Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

Thank you again! You have been of great help.

On Jan 28, 2:01 pm, JLGWhiz
wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub

" wrote:

Hi JLG,

The code is running this time without errors. But its not exactly
the
same thing it is suppossed to. It doesnt copy the information to
the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz
wrote:
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 -- Hide quoted text -- Show quoted text -- Hide
quoted text -- Show quoted text -- Hide quoted text --
Show quoted text -

If you only wanted to return "Day1" out of the sentence, it would
probably have to be some kind of a Len(Trim( ) combination . . .

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote in message
...
Thanks, Tom. We got the first one for just the Day1 entry. The this
question was posed:

question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract the
whole sentence to the other workbook.

It seemed to me that the Like operator with wildcards would work, and it
did
return the sentence "This is Day1 of many" for me in a test, but the OP
said
they didn't get the desired result. Since there was no further
explanation,
I give up.


"Tom Ogilvy" wrote:

If you only want to return Day1

Within your conditional code:
If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

then instead of doing

Activesheet.Paste

just do

ActiveCell.Value = "Day1"

Again, assuming only returning the value "Day1"
the whole thing could probably be done with countif to count the number
of
cells in the source range containing Day1,

then use special cells in the destination to get the blank cells and fill
the subset of those cells for the count returned by countif with Day1

But I doubt the OP just wants Day1.

--
Regards,
Tom Ogilvy




"JLGWhiz" wrote in message
...
Using the operator "Like" instead of "=" gave me a return of the
entire
sentence. If you only wanted to return "Day1" out of the sentence, it
would
probably have to be some kind of a Len(Trim( ) combination and I a not
that
adept with those functions.

" wrote:

Thanks again JLG, Thank you for all your help. I had already tried
doing"*Day1*" before, but it didnt work as expected.


On Jan 28, 6:44 pm, JLGWhiz wrote:
However, If you use this line:

If Workbooks(2).Sheets(1).Range("$G" & counter) Like "*Day1*" Then

Instead of this one:

If Workbooks(2).Sheets(1).Range("$G" & counter) = "Day1" Then

It might do what you are asking.



" wrote:
Hi JLG,

this last code works great! Thank you so much for your help. One
question: how would I make this work if there was a sentence
containing the word "Day1" in that cell so that I could extract
the
whole sentence to the other workbook.

Thank you again! You have been of great help.

On Jan 28, 2:01 pm, JLGWhiz
wrote:
See if this version is more suitable to your needs:

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
Range("$A$7").Activate
If ActiveCell = "" Then
ActiveSheet.Paste
Else
Do Until ActiveCell = "" Or counter = 14
ActiveCell.Offset(1, 0).Activate
Loop
ActiveSheet.Paste
End If
End If
counter = counter + 1
End If
Next
End Sub

" wrote:

Hi JLG,

The code is running this time without errors. But its not
exactly
the
same thing it is suppossed to. It doesnt copy the information
to
the
topmost empty table. Nevertheless the thing works without any
errors...

eg. if this is how the data is on wb2:

G10 Day1
G11 Day2
G12 Day2
G13 Day1

it gets copied on wb1 as:

A7 Day1
A8 (empty)
A9 (empty)
A10 Day1

On Jan 28, 9:07 am, JLGWhiz

wrote:
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 -- Hide quoted text -- Show quoted text -- Hide
quoted text -- Show quoted text -- Hide quoted
text --
Show quoted text -