Run-time error '1004 - extract range has a missing or illegal field name

  • 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


  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    M2 is the start of the column, (under the header), which has the references to be compiled into workbooks.


    I'm sorry, I don't understand your comment about clearing the copy to range?

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    But what is the contents of M2 - it does have something in it? Try this just before the AF line.

    Code
    sh.Range("A" & lr + 2).currentregion.clear

    If problems persist, I suggest you post a small workbook.

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    The ranges should include the header row.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    So I inserted like this:


    Code
    sh.Range("M2:M" & lr).AdvancedFilter xlFilterCopy, , sh.Range("A" & lr + 2).CurrentRegion.Clear, True


    But I got Run-time error '1004': reference is not valid. Apologies, as you have probably gathered my VBA knowledge has more than a few large gaps. I tend to get by, by finding script which looks as though it will do the job and modifying it but occasionally it just brings up a problem I can't work out.


    I'm attached a sample workbook as you suggested. This is just 200 lines. The actual workbook will have between 150,000 and 200,000 lines.

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    No I meant before it, and I have included M1 in the AF (though your code didn't error for me anyway).

    Code
    sh.Range("A" & lr + 2).CurrentRegion.Clear
    sh.Range("M1:M" & lr).AdvancedFilter xlFilterCopy, , sh.Range("A" & lr + 2), True
  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    Thank-you. That does seem to have solved that problem. However, now I get Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed. In the code I have in my workbook I just have the file path to a server folder. Could that be causing the problem? I know the file path is correct as it will take me to the correct folder if I paste it in Windows Explorer. What seems to be happening is that it creates the first new workbook, but then fails as it tries to save it.


    Code
    wb.SaveAs ThisWorkbook.Path & "\\server_name\server_folder\name\folder\FPDR extract files\Test Month\Created Files" & c.Value & ".xlsx"


    Many thanks for your advice. It is very much appreciated.

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    I tried adding in the backslash as you suggested but got the same error. I'm sorry, I don't understand what you mean by "what's the value of c when it errors?"

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    When it errors you should get an option to debug. Go into the editor and hover the mouse over c in that line and it should show you the value. Perhaps it's not a valid file name.

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    Ah, I see. So it says c.Value = "AEOBD_1214". This is the first unique value in column M from which the workbooks are being created and it's also the tab name on the first workbook it creates before error'ing so am guessing that's ok?

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    Just in case I tried changing the save as file path to my desktop. In case there was some strange thing with it not being able to access the server file path but I got the same error message.

  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    Ok, this is now working thanks to the assistance I received here and elsewhere. Very much appreciated. In case it is useful to anyone, the end code was:


  • Re: Run-time error '1004 - extract range has a missing or illegal field name


    Thanks for posting the code, but please make sure in future that you add links if you post questions elsewhere. This is one of the rules you signed up to Ozgrid (and most other forums).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!