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.
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