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/showthread.php?7688-Solved-ThisWorkbook-Path#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") & "\*****\"