Run-time error

[12hcks]

Hi all,

I am new to macro but just want to see if anyone has any expertise on below. I ran this code on another set of data and it worked. just wondering if there is anything wrong with the codes, as i suspect there are some blank cells in my data set causing my run-time error 1004. appreciate all your effort as I am not really familiar with this excel environment, and have a nice day.

Option Explicit

Sub separate_by_cru()

    Dim wb As Workbook, wbNew As Workbook
    Dim ws As Worksheet, wsData As Worksheet
    Dim rng As Range, arCRU As Variant
    Dim n As Long, iLastRow As Long
    Dim foldername As String, sName As String

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("CRU")
    iLastRow = ws.Cells(Rows.Count, "A").End(xlUp).Row
    arCRU = ws.Range("A2:A" & iLastRow).Value2

    Set wsData = wb.Sheets("Master")
    iLastRow = wsData.Cells(Rows.Count, "A").End(xlUp).Row
    Set rng = wsData.Range("A1:Y" & iLastRow)
   
    ' make folder for workbooks
    foldername = wb.Path & "\" & wb.Name & " " & Format(Now, "yyyy-mm-dd hh-mm-ss")
    MkDir foldername

    Application.ScreenUpdating = False
    For n = 1 To UBound(arCRU)
        sName = arCRU(n, 1)
        Set wbNew = Workbooks.Add(xlWBATWorksheet) ' 1 sheet
        wbNew.Sheets(1).Name = sName
        rng.AutoFilter Field:=17, Criteria1:=sName
        rng.Copy
        wbNew.Sheets(1).Paste
        wbNew.SaveAs foldername & "\" & sName & ".xlsx"
        wbNew.Close False
    Next

    wsData.AutoFilterMode = False
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox n & " files created in " & foldername, vbInformation

End Sub

 

[Alex Blakenburg]

Which line is highlighted when you click on Debug of the Error Message box ?

 

[12hcks]

Set wbNew = Workbooks.Add(xlWBATWorksheet) ' 1 sheet

Which line is highlighted when you click on Debug of the Error Message box ?

This is the one!

 

[Alex Blakenburg]

Can you give me a screen shot of the error message and the highlighted code (with the highlight showing)
I can make it fail on the autofilter and on the save as but not on workbooks.add.

 

[12hcks]

Hi Alex here you go, what do you mean by fail on the autofilter and on the save?

 

[Alex Blakenburg]

what do you mean by fail on the autofilter and on the save?

It means that I can't get it to fail on Workbooks.Add and that Workbooks.Add is a very unlikely place for it to fail

Set wbNew = Workbooks.Add(xlWBATWorksheet) ' 1 sheet

Failing on wbNew.Sheets(1).Name = sName makes a lot more sense.
It means that one of the Names you have Column A of Sheet CRU and are using to try and rename a sheet has characters in it that are invalid in a Sheet Name.

When the code errors out, hover over sName and see what name is in sName at the point of failure.
Alternatively when the code errors out, in the immediate window copy in the below with the question mark and hit enter. (this is while still in debug mode, don't hit stop on the macro first)

? sName

That should show the name that is invalid, in the immediate window.

If you don't see the immediate window when you are in VBA, type Ctrl+G to open it,

 

[12hcks]

thank you so much! it works now! however my outputs are strange, all the new sheets virtually have no content in it. Its new workbooks with no filtered content. Do you know why?

 

[12hcks]

thank you so much! it works now! however my outputs are strange, all the new sheets virtually have no content in it. Its new workbooks with no filtered content. Do you know why?

It works now! thanks a lot.

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.