Beatrix
09-21-2015, 11:35 AM
Hi Everyone ,
I am trying to create multiple workbooks from a list based on filtered data in column B. There are 56 unique items listed on the filter so I need 56 separate workbooks. I am using below macro which does the job but I need to make it dynamic to apply for all filtered items in one go. Can anyone help on how to do this ?
Cheers
B.
Criteria1:= "X"
ActiveCell.FormulaR1C1 = "X"
"C:\Users\vbax\X.xlsx"
Range("B3:B4").Select
Selection.AutoFilter
Range("B4").Select
ActiveSheet.Range("$B$3:$B$1045").AutoFilter Field:=1, Criteria1:= _
"X"
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Columns("A:Z").Select
Columns("A:Z").EntireColumn.AutoFit
Range("B4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "X"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\vbax\X.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Range("A4").Select
ActiveWindow.Close
End Sub
I am trying to create multiple workbooks from a list based on filtered data in column B. There are 56 unique items listed on the filter so I need 56 separate workbooks. I am using below macro which does the job but I need to make it dynamic to apply for all filtered items in one go. Can anyone help on how to do this ?
Cheers
B.
Criteria1:= "X"
ActiveCell.FormulaR1C1 = "X"
"C:\Users\vbax\X.xlsx"
Range("B3:B4").Select
Selection.AutoFilter
Range("B4").Select
ActiveSheet.Range("$B$3:$B$1045").AutoFilter Field:=1, Criteria1:= _
"X"
Cells.Select
Selection.Copy
Workbooks.Add
Cells.Select
ActiveSheet.Paste
Columns("A:Z").Select
Columns("A:Z").EntireColumn.AutoFit
Range("B4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "X"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\vbax\X.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Range("A4").Select
ActiveWindow.Close
End Sub