Macro not copying down lines

[Ophi]

I have a macro which is supposed to review the data in one workbook and then create several workbooks including the title row from the original workbook and the rows for each change in data in a certain column. So, for example, workbook one would create with the title rows and lines 2-10 and then workbook two would create with the title rows and lines 11-152, and so on, based on the fact that the break point / change in data to which it refers comes at lines 2 and 11 respectively.

This macro was created after much searching and with help, (including from this forum), but when I go to test it now I find that it is creating the workbooks and includes the title row but does not paste the data from the rows.

Would be grateful for advice.


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("A" & lr + 2).CurrentRegion.Clear
sh.Range("M1: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 & "\Created Files\" & 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

[p45cal]

Without actually trying it out on data, the only thing tht raises an eyebrow is the line:
sh.Range("A1", sh.Cells(lr, lc)).AutoFilter 12, c.Value
where the 12 I think represents column L, whereas I'm just guessing that you want to be filtering on column M (=column 13)?

[Ophi]

Many thanks for the response p45cal. I've tested your suggestion and I just get a Run-time error '1004': AutoFilter method of Range class failed. I think, in this instance, l2 refers to line 2, i.e. start the filtering at the row below the title row.

It is doing the filtering correctly and creating individual workbooks at the right break points and with the right names, (from the data in column M), but it is not then copying those lines into those new workbooks, i.e. I just get the workbooks with a header row and nothing else.

[p45cal]

I think, in this instance, l2 refers to line 2, i.e. start the filtering at the row below the title row.No. You've written l2 (L2 in upper case) in your last message, but your code definitely has 12 (twelve). It is the column number of the filter in the filtered range.
My suggestion was to change that line to:
sh.Range("A1", sh.Cells(lr, lc)).AutoFilter 13, c.Value

If this gives an error then check that the value of lc (LC in upper case!) is 13 or greater.

I can't help more working blind. I'd need a file where the error is occuring.

[Ophi]

My apologies, I misunderstood

I see what happened here now. A column has been added by someone who was testing which I failed to notice as I wasn't focusing so much on the whole, but rather the filter column in question. That coupled with my basic VBA understanding!

Works perfectly now. Thank-you very much :clap: