Macro to transfer data to other workbooks based on the value of two different cells

[CharlieZangel]

I need a macro to help me with data transfer from one workbook to others based on the value of a couple of cells.

This is my scenario:
In Workbook1, If B4 = the word "change" and F4 is less than or greater than zero, I would like Columns A:C and U:AB transfered into Workbook2
and If B4 = the word "Change" and F4 is equal to zero, I would like columns A:D and G:T transfered into Workbook3

I hope one of you gurus out there can help me with this. Thanks in advance for your help.

[JBeaucaire]

I think it's time for you to start developing code of your own and ask for assistance on it. Here's a pseudo method for you to work on.

1) Use any method you've learned so far to determine the LastRow of data (LR) on your master sheet
2) Choose an empty column to the right of your data and put a formula in it that tests all your rows for you all at once for the first criteria... in CA4:CA(LR) for instance:

=AND($B4="change", $F4>0)

That will put TRUE/FALSE results in that column.

3) Apply an AutoFilter to that sheet on that column to show only TRUE
4) Copy the entire visible range to your other sheet
5) Repeat steps 2-4 changing the formula to =0 then copying to worksheet3

You should be able to get yourself over half way there, stopping along the way to record yourself doing some parts, then adding that recorded code in to your macro as you develop it, tweaking the recorded code.

Show us some effort...


===============
Alternately, the method described above would allow you to do this manually in less the 2 minutes.

[CharlieZangel]

Hey JB, Thanks for the steps, I have been out for a little over a week now, but I will get back to you with what I come up with soon.
Thanks for the Challenge!

---------- Post added at 10:32 AM ---------- Previous post was at 09:17 AM ----------

This is what I have so far and I get an error msg in the highlighted code

Please Login or Register  to view this content.

[JBeaucaire]

Please Login or Register  to view this content.

This is nonsensical, there are no row references in this line of code.


If you're trying to put a FORMULA into your entire Emp range, then just do that:

Please Login or Register  to view this content.

[CharlieZangel]

Lol JB..still learning, will modify the code and let you know how I fare.

[CharlieZangel]

JB, You will be very proud of me....thanks for the push, it made the diffrence.

Here is what I wanted:
When cells BD = 0 and AB = "Endorsement", Columns BJ:CM is transferred to InsuredRecord File
When cells BD is <> 0 and AB = "Endorsememt",Columns I:BI is transferred to PolicyRecord File
When cells AB is not "Endorsement", Columns I:BI should be transfred to the next available row in the PolicyRecord File & columns BJ:CM should be transfered to the next available row in the InsuredRecord File.

for some reason I'm not able to get the transferring to the next available row code to work. It just transfers and replaces whatever data was there.

Here is my code

Please Login or Register  to view this content.

[JBeaucaire]

Since you already have the sheet listed in in the WITH reference, maybe:

Please Login or Register  to view this content.