VBA to filter and save as new workbook

[thedeadzeds]

Hi Guys,

I have the following worksheet. I would like to be able to do the following if possible:

Filter by every Name (column a) and then save columns A to Z in as a new workbook in a new folder. So for the below example, in the folder titled 'Names', there would be 3 workbooks, 1 for Dave with only Daves data in it, 1 for Jim and so on.

Many thanks

Name	Date	Product	Type	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
Dave	Oct-20	Product 1	Core	Product 1	£1.00	£2.00	94.2%	295	£295.00	5.8%	18	£36.00	313	£331.00	80.0%	250	£250.40	20.0%	63	£125.20	313	£375.60		Normal	£650.00
Dave	Oct-20	Product 2	Core	Product 2	£1.00	£2.00	91.4%	169	£169.00	8.6%	16	£32.00	185	£201.00	80.0%	148	£148.00	20.0%	37	£74.00	185	£222.00		80%	£727.20
Dave	Oct-20	Product 3	Core	Product 3	£1.00	£2.00	88.5%	77	£77.00	11.5%	10	£20.00	87	£97.00	80.0%	70	£69.60	20.0%	17	£34.80	87	£104.40
Dave	Oct-20	Product 4	Core	Product 4	£1.00	£2.00	100.0%	1	£1.00	0.0%	0	£0.00	1	£1.00	80.0%	1	£0.80	20.0%	0	£0.40	1	£1.20
Dave	Oct-20	Product 5	Core	Product 5	£1.00	£2.00	100.0%	4	£4.00	0.0%	0	£0.00	4	£4.00	80.0%	3	£3.20	20.0%	1	£1.60	4	£4.80
Dave	Oct-20	Product 6	Core	Product 6	£1.00	£2.00	100.0%	2	£2.00	0.0%	0	£0.00	2	£2.00	80.0%	2	£1.60	20.0%	0	£0.80	2	£2.40
Dave	Oct-20	Product 7	Core	Product 7	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Dave	Oct-20	Product 8	Upsell	Product 8	£1.00	£2.00	100.0%	1	£1.00	0.0%	0	£0.00	1	£1.00	80.0%	1	£0.80	20.0%	0	£0.40	1	£1.20
Dave	Oct-20	Product 9	Upsell	Product 9	£1.00	£2.00	100.0%	4	£4.00	0.0%	0	£0.00	4	£4.00	80.0%	3	£3.20	20.0%	1	£1.60	4	£4.80
Dave	Oct-20	Product 10	Upsell	Product 10	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Dave	Oct-20	Product 11	Upsell	Product 11	£1.00	£2.00	100.0%	1	£1.00	0.0%	0	£0.00	1	£1.00	80.0%	1	£0.80	20.0%	0	£0.40	1	£1.20
Dave	Oct-20	Product 12	Upsell	Product 12	£1.00	£2.00	100.0%	5	£5.00	0.0%	0	£0.00	5	£5.00	80.0%	4	£4.00	20.0%	1	£2.00	5	£6.00
Dave	Oct-20	Product 13	Upsell	Product 13	£1.00	£2.00	100.0%	3	£3.00	0.0%	0	£0.00	3	£3.00	80.0%	2	£2.40	20.0%	1	£1.20	3	£3.60
Dave	Oct-20	Product 14	Upsell	Product 14	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Dave	Oct-20	Product 15	Upsell	Product 15	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Dave	Oct-20	Product 16	Upsell	Product 16	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Dave	Oct-20	Product 17	Upsell	Product 17	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Dave	Oct-20	Product 18	Upsell	Product 18	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 1	Core	Product 1	£1.00	£2.00	99.0%	102	£102.00	1.0%	1	£2.00	103	£104.00	80.0%	82	£82.40	20.0%	21	£41.20	103	£123.60		Normal	£222.00
Jim	Oct-20	Product 2	Core	Product 2	£1.00	£2.00	98.4%	60	£60.00	1.6%	1	£2.00	61	£62.00	80.0%	49	£48.80	20.0%	12	£24.40	61	£73.20		80%	£264.00
Jim	Oct-20	Product 3	Core	Product 3	£1.00	£2.00	100.0%	55	£55.00	0.0%	0	£0.00	55	£55.00	80.0%	44	£44.00	20.0%	11	£22.00	55	£66.00
Jim	Oct-20	Product 4	Core	Product 4	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 5	Core	Product 5	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 6	Core	Product 6	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 7	Core	Product 7	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 8	Upsell	Product 8	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 9	Upsell	Product 9	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 10	Upsell	Product 10	£1.00	£2.00	100.0%	1	£1.00	0.0%	0	£0.00	1	£1.00	80.0%	1	£0.80	20.0%	0	£0.40	1	£1.20
Jim	Oct-20	Product 11	Upsell	Product 11	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 12	Upsell	Product 12	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 13	Upsell	Product 13	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 14	Upsell	Product 14	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 15	Upsell	Product 15	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 16	Upsell	Product 16	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 17	Upsell	Product 17	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Jim	Oct-20	Product 18	Upsell	Product 18	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 1	Core	Product 1	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00		Normal	£0.00
Steve	Oct-20	Product 2	Core	Product 2	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00		80%	£0.00
Steve	Oct-20	Product 3	Core	Product 3	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 4	Core	Product 4	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 5	Core	Product 5	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 6	Core	Product 6	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 7	Core	Product 7	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 8	Upsell	Product 8	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 9	Upsell	Product 9	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 10	Upsell	Product 10	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 11	Upsell	Product 11	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 12	Upsell	Product 12	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 13	Upsell	Product 13	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 14	Upsell	Product 14	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 15	Upsell	Product 15	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 16	Upsell	Product 16	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 17	Upsell	Product 17	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00
Steve	Oct-20	Product 18	Upsell	Product 18	£1.00	£2.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00	0.0%	0	£0.00	0.0%	0	£0.00	0	£0.00

 

[Marc L]

Hi,​

​

according to Excel basics it should be easily achieved if only all the columns have an unique header even any blank column …​

​

Anyway, is column A always sorted before to run the VBA procedure ? (As if yes there is another way …)​

 

[thedeadzeds]

Hi,​

​

according to Excel basics it should be easily achieved if only all the columns have an unique header even any blank column …​

​

Anyway, is column A always sorted before to run the VBA procedure ? (As if yes there is another way …)​

Hi, yes Column A will awlays be sorted by Name

 

[Marc L]

According to your attachment a VBA demonstration as a beginner starter where the destination folder must already exist,​

the sort of column A is added for safety, to paste to the worksheet module :​

Sub Demo1()
     Dim P$, Ws As Worksheet, L&, F&, N$
         P = ThisWorkbook.Path & "\Names\"
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
         Set Ws = Workbooks.Add.Worksheets(1)
    With [A1].CurrentRegion.Resize(, 26).Rows
           .Item(1).Copy Ws.[A1]
           .Sort .Cells(1), 1, Header:=xlYes
            L = 1
        While Not IsEmpty(.Cells(L + 1, 1))
            Ws.UsedRange.Offset(1).Clear
            F = L + 1
            N = .Cells(F, 1).Text
            L = .Columns(1).Find(N, , , xlWhole, , 2).Row
           .Item(F & ":" & L).Copy Ws.[A2]
            Ws.Parent.SaveAs P & N, 51
        Wend
    End With
         Ws.Parent.Close False
        .Speech.Speak "Done !", True
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

 

[thedeadzeds]

According to your attachment a VBA demonstration as a beginner starter where the destination folder must already exist,​

the sort of column A is added for safety, to paste to the worksheet module :​

Sub Demo1()
     Dim P$, Ws As Worksheet, L&, F&, N$
         P = ThisWorkbook.Path & "\Names\"
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
         Set Ws = Workbooks.Add.Worksheets(1)
    With [A1].CurrentRegion.Resize(, 26).Rows
           .Item(1).Copy Ws.[A1]
           .Sort .Cells(1), 1, Header:=xlYes
            L = 1
        While Not IsEmpty(.Cells(L + 1, 1))
            Ws.UsedRange.Offset(1).Clear
            F = L + 1
            N = .Cells(F, 1).Text
            L = .Columns(1).Find(N, , , xlWhole, , 2).Row
           .Item(F & ":" & L).Copy Ws.[A2]
            Ws.Parent.SaveAs P & N, 51
        Wend
    End With
         Ws.Parent.Close False
        .Speech.Speak "Done !", True
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
End Sub

Thanks for this. Ive created the folder 'Names' in the same path as the workbook but nothing seems to happen. I can hear 'done' so its run through the code, but I cant see the new individual worksheets anywhere

 

[Marc L]

​

As this code works only with good enough readers so well follow the dark read direction of my previous post …​

​

If the code is located where it must be, as it works as expected on my side​

so you must investigate on VBE side in debug step-by-step mode hitting F8 key​

in order to follow the execution, check the variables contents and see what happens …​