Filter - Copy - Paste

[BOB72120]

Hello R1C1,

I am beginner in excel VBA coding and found a problem related to this, I am having a sheet where the data is available state wise, and i want to put a code which will help to filter the data state wise and copy the selected data and paste into new excel workbook and then save the same from the name of that state in specific folder and repeat the same action for rest of the data until it's covered all the filtered data.

Your help will be a breakthrough to save my job.

Thanks,
Pranav Sharma

[Paul_Hossler]

You are posting to a 12 year old topic with a title that doesn't seem to apply to your question

You'd get more visibility if you create a new topic with a more appropriate title

[SamT]

These first three posts moved from:
http://www.vbaexpress.com/forum/show...ath#post377894

[mana]

Option Explicit


Sub test()
    Dim r As Range
    Dim c As Range
    Dim p As String
    
    Set r = Range("A1").CurrentRegion
    Set c = r(1).Offset(, r.Columns.Count + 1)
    
    p = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\*****\"
    
    r.Columns(1).AdvancedFilter xlFilterCopy, , c, True
    Do While c.Offset(1).Value <> ""
        With Workbooks.Add(xlWBATWorksheet)
            r.AdvancedFilter xlFilterCopy, c.Resize(2), .Sheets(1).Range("A1")
            .SaveAs p & c.Offset(1).Value & ".xlsx", xlOpenXMLWorkbook
            .Close False
        End With
        c.Offset(1).Delete xlShiftUp
    Loop
    c.Resize(2).ClearContents
    
End Sub

マナ

[BOB72120]

Attaching the excel, if you can help to put a code in VBA for this will be very thankful. In attached excel I want to filter the data from column F "Brand"and then select 1 by 1 each brand and copy the data and paste into new excel and save into a specific folder with the same name of the brand on my desktop. And the same action keep on continue until the all brands are completed.

[mana]

r.Columns("F").AdvancedFilter xlFilterCopy, , c, True

[BOB72120]

Yes, auto filter need to put on column "F" and macro will copy and paste the data brand wise into new excel with the same brand name in specific folder.

[mana]

>in specific folder.

p = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\*****\"