PDA

View Full Version : Filter - Copy - Paste



BOB72120
03-15-2018, 06:38 AM
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
03-15-2018, 12:15 PM
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
03-17-2018, 02:05 PM
These first three posts moved from:
http://www.vbaexpress.com/forum/showthread.php?7688-Solved-ThisWorkbook-Path#post377894

mana
03-17-2018, 07:13 PM
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
03-18-2018, 09:10 PM
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
03-24-2018, 02:58 AM
r.Columns("F").AdvancedFilter xlFilterCopy, , c, True

BOB72120
03-24-2018, 08:23 PM
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
03-25-2018, 04:09 AM
>in specific folder.


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