VBA Help - Search, Match, Copy

[jmack46]

Hi,
What I have is a data sheet. In column A are the vendor names, B is PO #, C is Item #, D is Order Qty, E is unit cost, F is extended cost Then, there are sheets named for each vendor. What I would like to accomplish is matching vendor name in column A to the sheet named for that vendor, then copy and paste info. PO # will be a constant for each vendor, but there will be multiple items, qtys, etc.
Below is a snapshot of where the data goes on the destination sheet:

Column B --> "C2"
Column C --> A2 & fill down
Column D --> B2 & fill down
Column E --> U2 & fill down
Column F --> V2 & fill down

I found code for simple match and fill by row, but this one is a little special since the source and destination are not an exact format match. Any help is greatly appreciated.

[xladept]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jmack46]

I hope I have attached the file correctly. First tab is the Summary, where all data is contained. The following tabs are generated using VBA and the final step is to add to that code to then populate the tabs with the order info. In the first tab, I have populated the fields as the result should look.

[xladept]

Try this:

Please Login or Register  to view this content.

[jmack46]

Brilliant! Works like a charm! Thank you!

[xladept]

You're welcome!


Please click on Thread Tools and mark as Solved

[jmack46]

Sorry, I hate to bother... I 'assumed' I would be able to modify the code written for the TEST file to work on my working file... unfortunately, some of the functions you used are WAY too advanced for me and I just cannot follow it well enough to modify. It really comes down to the source and destination locations being in different locations on the working file.
I have reformatted the TEST file to be an exact match of the working file (source and destination). I really appreciate your expertise here.

[xladept]

For the new sample:

Please Login or Register  to view this content.

And, thanks for the rep!

[jmack46]

Hi,
Thank you so much. The new sample code works... and I see now how the cell reference work. However, when I put this into the working file, I am getting a subscript out of range error on this line:
Z = Split(J, "~"): Set wv = Sheets(Replace(Z(0), " ", "") & Z(1))
The Bold section is where the issue is. I do have other sheets in the working file... one is a master report, one is a template sheet that is used to create the vendor order forms and the last one is a data source file for the item codes.
I suspect those other sheets are a problem?

[xladept]

The other sheets shouldn't be a problem. The error has to do with the sheet name. Either the sheet isn't named "VendorA123" or the Vendor and sequence number are different on the summary sheet.

[xladept]

Correction:

Please Login or Register  to view this content.

[jmack46]

Thanks! I know what's wrong now... the Vendor column on the working file is a formula =CONCATENATE($P2,$R2)... when I copy and paste as text, it works. So, I think I'll have to insert a hidden column and then add a function in the macro to copy and paste as text to "Q" as the first step... does that sound like to correct solution?

[jmack46]

Actually, forget all of that... I think my brain is done for today! It wasn't even what I thought... it was something really riduculously dumb... Thank you so much for your time and help with this! It is working perfectly now!

[xladept]

Things often work out that way - glad it's OK now!