• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a macro for filtering and saving as different workbooks

sreekhosh

sreekhosh

Member
Hi,


My mastersheet having 5 company names (A,B,C,D,E) and its transactions on date wise (suppose 10 txns each). I need a macro for filter the data with company names, copy the filtered data to a new excel file (paste as values) and save this new file with the selected company name (add system Date also with the file name).


Saving path :D/daily/reports


Thanks


Sreekhosh
 
Or have a look right here: http://chandoo.org/wp/2012/05/14/vba-move-data-from-one-sheet-to-multiple-sheets/
 
Thanks Luke and Hui,


Please correct the below .


Sub Macro1()


Windows("Customer No Extraction.xlsb").Activate

Sheets("Not Updated").Select


Range("a1").Select


'=======FILTERING=======


For i = 0 To 5


fcr = Array("A","B","C","D","E")


ActiveSheet.Range("$C$1:$E$10000").AutoFilter Field:=3, Criteria1:=fcr(i)


Range("B1:F10000").Select

Range("C1").Activate

Selection.Copy

Workbooks.Add

Range("A1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Application.CutCopyMode = False


'=========SAVING======= I NEED TO SAVE THE file WIH "A"+systemdate IN BELOW PATH, then "B"+systemdatte,=======


ActiveWorkbook.SaveAs filename:="Z:DRSfcr(i)&format(date,"ddmmyy")&.xlsx"


Next i


End Sub


Here I put the file name as fcr(i) but it is saved as fcr(i).xlsx not a.xlsx,


Note: I don't have any basic of VB and any programming language.


Thanks


Sreekhosh
 
Hello Shrekosh,


One more i see at http://datapigtechnologies.com/downloads/DATAPIGEE.zip


This is an add-in and do the job.

-----------------------------------------------------


How to install DataPig Excel Explosion


1. Download the DataPig Excel Explosion Add-in.


2. Unzip the file. This will install the needed files into the C:DPEE Directory


3. Start Excel.


4. Select Tools from the main menu, then select Add-Ins.


4. In the Add-Ins dialog box, click the Browse button.


5. Locate the C:DPEE folder and select the datapigee.xla file


6. Excel's Main menu will now display a new menu item: 'DATAPIG'. Click on 'DATAPIG' to get started.


DataPig Excel Explosion will be available for all future Excel sessions.


To Uninstall the add-in, choose Add-Ins from the Tools menu, and remove the checkmark from the "Datapigee" item

-------------------------------------------------------

Regards,

KPJ
 
Hi Sreekhosh,


I'd suppose you need to change line:

Code: 
ActiveWorkbook.SaveAs filename:="Z:DRSfcr(i)&format(date,"ddmmyy")&.xlsx"


To

ActiveWorkbook.SaveAs filename:="Z:DRS" & fcr(i) & format(date,"ddmmyy")&".xlsx"


When posting on forum, try to use backtick character so the code gets differently formatted.
 
You must log in or register to reply here.
Back
Top