filter a excel docuement using VBA and export results
Posted on 2014-09-26
I want to filter a excel doc, and save the results in a new .xlsx (same directory) with the current filename_filtervalue.xlsx
I.E. current filename is "new", and I am filtering in column C with the text "grass", I want the macro to save the file as: "new_grass.xlsx" in the same directory as the active workbook.
1) the parameters that I will be filtering will change every time, so I want the VBA to look at "first choice" to filter, then save, look at the "second choice" to filter than save, look at the "third choice" to filter then save etc.
2) I think the vba can reference what is in cell C1 after the filter is applied to know what filter value to add to the filename.
3) FYI I am using the following code to create the initial workbook that will be filtered. This workbook was originally a .csv that will be saved as current filename.xlsx after I run the first part of the macro.
Public Function s_as(xlsx As String) As String
s_as = ActiveWorkbook.FullName
s_as = Left(s_as, InStrRev(s_as, ".")) & xlsx
ActiveWorkbook.SaveAs FileName:=s_as("xlsx"), FileFormat:=xlOpenXMLWorkbook
4) I will not need code examples of how to cut and paste the results in the new .xlsx,
just help with the filtering, and the save as of the new file.
5) IF you could give me the sub proceedure code/function code as well as the usage code (how it will be called out from another module) that would be greatly appreciated.