Hi,
I am quite new to VBA but learning (slowly). I have a table in cells A20:CM520, the first 19 rows are instructions and a header which I wish to keep if possible. I would like to filter by column X and paste the visible cells (preserving their format) to a new worksheet for each name filtered in column X (using that name as the worksheet name). I have got this code so far but am struggling to get it up and running, apologies for my inexperience and thank you in advance for your help. I am using Excel 2010:
Sub SplitWorkbook()
Rows("19:19").Select
Range("AO19").Activate
ActiveSheet.Range("$A$19:$CN$572").AutoFilter Field:=24,
Cells.Select
Range("K1").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
Application.CutCopyMode = False
ActiveSheet.Paste
ActiveWindow.Close
ActiveSheet.Range("$A$19:$CN$572").AutoFilter Field:=24
Range("A1").Select
End Sub
Thank you so much in advance! If I can crack this it will save me such a huge amount of time.
Thanks,
Lald
I am quite new to VBA but learning (slowly). I have a table in cells A20:CM520, the first 19 rows are instructions and a header which I wish to keep if possible. I would like to filter by column X and paste the visible cells (preserving their format) to a new worksheet for each name filtered in column X (using that name as the worksheet name). I have got this code so far but am struggling to get it up and running, apologies for my inexperience and thank you in advance for your help. I am using Excel 2010:
Sub SplitWorkbook()
Rows("19:19").Select
Range("AO19").Activate
ActiveSheet.Range("$A$19:$CN$572").AutoFilter Field:=24,
Cells.Select
Range("K1").Activate
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Workbooks.Add
Application.CutCopyMode = False
ActiveSheet.Paste
ActiveWindow.Close
ActiveSheet.Range("$A$19:$CN$572").AutoFilter Field:=24
Range("A1").Select
End Sub
Thank you so much in advance! If I can crack this it will save me such a huge amount of time.
Thanks,
Lald