Macro to create PO's based on vendor?

Copper Contributor

I want to create a macro that copies items from a master PO sheet (3_Master PO Request)) to a standardized PO form (4A_PO Template) but I want this to occur by vendor. Additionally, I would like if items transferred to the standardized PO template would be removed from the list and then added to another sheet that has a running total for the year (4B_2024_CB3 PO Totals). How could I do this? I am mostly concerned with transferring from 3_Master PO Request to the 4A_PO Template. I've included a sample file. Thanks in advance :)

3 Replies

@qplsn9 I couldn't help but notice this is your third post now on this matter...

 

Feb 9thhttps://techcommunity.microsoft.com/t5/excel/macro-to-copy-items-from-one-sheet-to-another-by-vendor... 

 

Feb 26thhttps://techcommunity.microsoft.com/t5/excel/macro-to-copy-items-from-one-sheet-to-another-by-vendor... 

 

I think the main reason you're not getting any help is because the overall structure of the workbook, including the setup of the worksheet data and the existing VBA code, is poor. The column headers are inconsistent from one sheet to the next, there's no "Unit Price" found in the "3_Master PO Request" sheet, and it's unclear what "Qty" should be ordered for each item (how many over the Order Point do you want to order). Plus, the macros assigned to the "Export" and "Paste Values" buttons don't exist in this file... they are linked to another workbook: 'C:\Users\username\Desktop\...\filename.xlsm'. Looping through the data by vendor, 21 records at a time, is possible, but the current setup is so disjointed and there are too many unknowns for anyone to be able to help.

 

Even the code I helped you with previously (CopyRecordsByDisplayFormat), which was done blindly before I saw the actual file, I would do differently now. Advanced Filter would be a far more efficient (and simpler) method to use but would require the column headers to match from one worksheet to the next. The "Format" macro you have assigned to the "Filter" button changes the column headers to something other than their original values, which is odd because they don't even match the headers used in the final "4A_PO TEMPLATE" worksheet.

 

Paul Kelly has a number of videos on his YouTube channel, Excel Macro Mastery, that might help point you in the right direction:

 

Kind regards.

thank you for your reply. really just wanted a general skeleton of how to loop through and copy data from one sheet to two others by vendor. if you could. please remove everything after "C:" from your comment as it includes my personal information. thank you for your help!
Not sure if you're willing to help after already providing so much great information, but I simplified the Excel file and standardized all names across the sheets. All I want is the information from "Master" to be copied to "PO Template" (Catalog No., Name/Descr., Vendor, UOM, Order Point Qty." by Vendor, 21 items at a time, and then copies the information (Catalog No., Name/Descr., Vendor, UOM, Order Point Qty, Unit Price & Total [from PO Template]) to "PO Totals" at the next blank line and removes the copied data from "Master". I've included the updated file in my original post. Again, thank you for all your help!