[RobAndrews]

Hi,

I'm looking to set up a macro to do the following:

Open up a workbook called Works Order Register
Find my Works Order on the register by matching the WOnumber (I8) and Contractor name (C1735) from Works Order workbook.
Once found copy various data e.g cell I1735, I1737 etc across to columns D, E etc on the register against the corresponding row found in the previous step.

I've got parts working ok, but not the whole thing together.

Thanks for help.

[Rollin_Again]

Can you post a sample workbook so we can see the data layout? If you prefer not to post on the forum I can give you my email address to send to. The coding should be easy.

Regards,
Rollin

[RobAndrews]

I can email it to you. Would you like both workbooks involved?

[Rollin_Again]

Yes please. Send to Rollin_Again at hotmail dot com

Regards,
Rollin

[Rollin_Again]

OK, got your email and have a question. Are the work order number on the Register workbook unique? In other words, can we just perform a search for the work order number by itself or do we need to do a combination search using both the work order number and the contractor name?

Regards,
Rollin

[RobAndrews]

The works orders could be duplicated. A failing of the current system is that it raises a different work order number to each contractor, therefore I'm looking to do a register where one works order number can be issued to more than one contractor and thus capturing the whole job.

So in answer to your question, the works order number and contractor combination will be unique, not just the works order number alone.

The stage after the registration of the works order is to add another contractor stage (if applicable) which will then create another line on the register for that contractor.

[RobAndrews]

^bump

Back in the office now, anyone able to help on this one please?

[slurpee55]

This really sounds like it should be in Access or another database program. Any results you want can be easily exported to Excel, if you prefer.

[RobAndrews]

I'm sure you're correct but access isn't widely used by others in my workplace and I've got a number of macros working within this spreadsheet already.

Access is certainly a medium to long term option once I've got this quick fix solution completed.

Thanks though

[RobAndrews]

Originally Posted by RobAndrews

The works orders could be duplicated. A failing of the current system is that it raises a different work order number to each contractor, therefore I'm looking to do a register where one works order number can be issued to more than one contractor and thus capturing the whole job.

So in answer to your question, the works order number and contractor combination will be unique, not just the works order number alone.

The stage after the registration of the works order is to add another contractor stage (if applicable) which will then create another line on the register for that contractor.

Just to note, actually at this stage the works order isn't duplicated. I can add that into a macro a couple of stages on when it's really necessary.

[turbodante]

Sounds like you could write the VBA to do a Autofilter on the WONumber (I8) and Contractor name (C1735), then copy and pasting.

Do you want to post a sample of what you have (w/ dummy data) on the forum in case Rollin is not back for a while?