Hello
I have been tasked with finding a way to split up a spreadsheet within one worksheet into multiple workbooks based upon criteria in a specific column, i.e. creating a new workbook containing all lines that have unique criteria in that column. I have managed to find some code which looks like it should do it but am getting Run-time error '1004 - extract range has a missing or illegal field name on line "sh.Range("M2:M" & lr).AdvancedFilter xlFilterCopy, , sh.Range("A" & lr + 2), True". Despite searching I can't find out why. I know that the first sh.Range has to be the column from which the new workbook titles will come, which it is. Code below - I have removed the file path for the purposes of posting here.
Advice very much appreciated.
Many thanks
Sub splitCUbycolumn()
Dim wb As Workbook, sh As Worksheet, ssh As Worksheet, lr As Long, rng As Range, c As Range, lc As Long
Set sh = Sheets(1)
lr = sh.Cells(Rows.Count, "M").End(xlUp).Row
lc = sh.Cells.Find("*", , xlFormulas, xlPart, xlByColumns, xlPrevious).Column
sh.Range("M2:M" & lr).AdvancedFilter xlFilterCopy, , sh.Range("A" & lr + 2), True
Set rng = sh.Range("A" & lr + 3, sh.Cells(Rows.Count, 1).End(xlUp))
For Each c In rng
Set wb = Workbooks.Add
Set ssh = wb.Sheets(1)
ssh.Name = c.Value
sh.Range("A1", sh.Cells(lr, lc)).AutoFilter 12, c.Value
sh.Range("A1", sh.Cells(lr, lc)).SpecialCells(xlCellTypeVisible).Copy ssh.Range("A1")
sh.AutoFilterMode = False
wb.SaveAs ThisWorkbook.Path & "\" & c.Value & ".xlsx"
wb.Close False
Set wb = Nothing
Next
sh.Range("A" & lr + 2, sh.Cells(Rows.Count, 1).End(xlUp)).Delete
End Sub
Display More