Need Macro to create separate Excel file by Vendor Names

[PaigeWarner]

Hi- I need help with finding coding for a Macro to create separate files by Vendor Names based in a spreadsheet like below. So with the below as an example 11 files would be created with only that specific Vendors lines on it. I would want the files to be put on a path in my Pdrive named:

\\cscfiles01\public$\Information Integrity\EDI Clean Up\SAP EDI Workflow Files\Material # and UOM Errors\855 Vendor Files

and I would want the files to have the following name convention:

855 EDI Errors_{Vendor Name}_{Todays Date}

Example:

855 EDI Errors_ABC, INC._20221106

Vendor ID	Vendor Name	HDS Material #	Vendor Material # (MIC)	Material Description	POA Error Date	POA Number	PO Number	PO Line Item	Error ID	Error Message	Received on 855 PO Acknowledgement (POA)	Expected on 855 PO Acknowledgement (POA)	Which value is correct?	Reason for Change
24786	ABC, INC.	12345	3789749	ABC Product 1	10/20/2022	7897345093	4398540985	3	ME740	User DA065715 already processing Sales Document W217102754	DA065715	Sales Document
99876	BCF PRODUCTS INC.	12364	3789749	ABC Product 20	10/20/2022	7897345112	4398541004	1	ME733	Material number 89100 differs from that of ordered material 7079889100	89100	7079889100
26894	DEF Incorporated	12346	3789749	ABC Product 2	10/20/2022	7897345094	4398540986	10	ME735	Price outside tolerance range (order price 128.60, new price 135.07)	128.60	135.07
18394	GHI MFG CO INC	12350	3789749	ABC Product 6	10/20/2022	7897345098	4398540990	1	ME732	Price outside tolerance range (order price 53.50, new price 40.13)	53.50	40.13
18394	GHI MFG CO INC	12351	3789749	ABC Product 7	10/20/2022	7897345099	4398540991	2	ME749	Price outside tolerance range (order price 98.99, new price 61.15)	98.99	61.15
22773	JKL MANUFACTURING, INC.	12355	3789749	ABC Product 11	10/20/2022	7897345103	4398540995	10	ME736	Price outside tolerance range (order price 36.82, new price 90.65)	36.82	90.65
22773	JKL MANUFACTURING, INC.	12353	3789749	ABC Product 9	10/20/2022	7897345101	4398540993	3	ME751	Price outside tolerance range (order price 56.42, new price 0.00)	56.42	0.00
22773	JKL MANUFACTURING, INC.	12352	3789749	ABC Product 8	10/20/2022	7897345100	4398540992	7	ME752	Price outside tolerance range (order price 3.52, new price 2.18)	3.52	2.18
22773	JKL MANUFACTURING, INC.	12354	3789749	ABC Product 10	10/20/2022	7897345102	4398540994	9	ME753	Price outside tolerance range (order price 28.28, new price 0.00)	28.28	0.00
77384	MNO PRODUCTS.INC	12356	3789749	ABC Product 12	10/20/2022	7897345104	4398540996	10	ME737	Price outside tolerance range (order price 71.63, new price 0.00)	71.63	0.00
77384	MNO PRODUCTS.INC	12357	3789749	ABC Product 13	10/20/2022	7897345105	4398540997	10	ME738	Price outside tolerance range (order price 88.35, new price 0.00)	88.35	0.00
77384	MNO PRODUCTS.INC	12358	3789749	ABC Product 14	10/20/2022	7897345106	4398540998	10	ME739	Price outside tolerance range (order price 49.40, new price 0.00)	49.40	0.00
77384	MNO PRODUCTS.INC	12359	3789749	ABC Product 15	10/20/2022	7897345107	4398540999	10	ME740	Price outside tolerance range (order price 69.16, new price 0.00)	69.16	0.00
88444	PQR ACCESSORIES, INC	12360	3789749	ABC Product 16	10/20/2022	7897345108	4398541000	10	ME741	Price outside tolerance range (order price 14.09, new price 16.27)	14.09	16.27
88444	PQR ACCESSORIES, INC	12361	3789749	ABC Product 17	10/20/2022	7897345109	4398541001	10	ME742	Price outside tolerance range (order price 34.19, new price 39.49)	34.19	39.49
88444	PQR ACCESSORIES, INC	12362	3789749	ABC Product 18	10/20/2022	7897345110	4398541002	10	ME743	Price outside tolerance range (order price 31.88, new price 36.82)	31.88	36.82
88444	PQR ACCESSORIES, INC	12363	3789749	ABC Product 19	10/20/2022	7897345111	4398541003	10	ME744	Price outside tolerance range (order price 22.76, new price 26.29)	22.76	26.29
44421	STU, INC.	12367	3789749	ABC Product 23	10/20/2022	7897345115	4398541007	1	ME734	Material number 4582597 differs from that of ordered material REN05647-US	4582597	REN05647-US
44421	STU, INC.	12368	3789749	ABC Product 24	10/20/2022	7897345116	4398541008	10	ME746	Material number 04578. differs from that of ordered material 4578	04578.	4578
44421	STU, INC.	12366	3789749	ABC Product 22	10/20/2022	7897345114	4398541006	14	ME748	Material number 02853280. differs from that of ordered material 2853280	02853280.	2853280
92048	TUV Manufacturing Products	12347	3789749	ABC Product 3	10/20/2022	7897345095	4398540987	7	ME729	Item 00000 of purchasing document 5110911934 does not exist	00000	5110911934
92048	TUV Manufacturing Products	12348	3789749	ABC Product 4	10/20/2022	7897345096	4398540988	4	ME730	Item 00000 of purchasing document 5110917756 does not exist	00000	5110917756
92048	TUV Manufacturing Products	12349	3789749	ABC Product 5	10/20/2022	7897345097	4398540989	2	ME731	Item 00000 of purchasing document 5110929097 does not exist	00000	5110929097
99983	WRX Design	12369	3789749	ABC Product 25	10/20/2022	7897345117	4398541009	10	ME747	Item 00010 has been deleted from document 5110032451	5110032451	00010
99983	WRX Design	12370	3789749	ABC Product 26	10/20/2022	7897345118	4398541010	20	ME750	Item 00020 has been deleted from document 5110684656	5110684656	00020
64355	XYZ FAUCET CO	12365	3789749	ABC Product 21	10/20/2022	7897345113	4398541005	10	ME745	Price outside tolerance range (order price 65.80, new price 133.30)	65.80	133.30

 

[kevin9999]

Hi Paige,

The trick in getting this type of code to work is all in the path name. The code below (assuming it's run from the file with all the data in it) uses ThisWorkbook.Path - which means it will 'split' the data into individual workbooks & save them in the same folder. I can't test the path you specify in your OP because such a path doesn't exist on my laptop. You'll have to play around with the path name (I indicated where in the code) to get it right. You'll also have to change the date format to suit what you want. Let me know how you get on.

Option Explicit
    Sub PaigeWarner()
    Application.ScreenUpdating = 0
    Dim Ws As Worksheet
    Set Ws = Worksheets("Sheet1")   '<< change to actual sheet name
    Dim Rng As Range
    Set Rng = Ws.Range("B2", Ws.Cells(Rows.Count, "B").End(xlUp))
    
    Dim Wb As Workbook, ArrIn, i As Long
    ArrIn = WorksheetFunction.Unique(Rng)
    
    If Ws.AutoFilterMode Then Ws.AutoFilter.ShowAllData
    For i = LBound(ArrIn) To UBound(ArrIn)
        With Ws.Range("A1").CurrentRegion
            .AutoFilter 2, ArrIn(i, 1)
            Set Wb = Workbooks.Add(1)
            .Copy Wb.Sheets(1).Cells(1)
            
            '***** This next line is the key - it's all in the path name *****
            '***** Perhaps something like, (only a guess)
            'Wb.SaveAs P:\cscfiles01\public$\Information Integrity\EDI Clean Up\SAP EDI Workflow Files\Material # and UOM Errors\855 Vendor Files" _
            '& "\855 EDI Errors_" & ArrIn(i, 1) & "_" & Format(Now, "dd mm yyyy") & ".xlsx", FileFormat:=51
            
            Wb.SaveAs ThisWorkbook.Path & "\855 EDI Errors_" & ArrIn(i, 1) & "_" & Format(Now, "dd mm yyyy") & ".xlsx", FileFormat:=51
            
            Wb.Sheets(1).UsedRange.Columns.AutoFit
            Wb.Close 1
            .AutoFilter
        End With
    Next i
Application.ScreenUpdating = 1
End Sub