Posts by Ophi

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


    StephenR noted and I would have done had I gone to another forum at the time I first posted. As it was, I only posted elsewhere when I was trying to get to the bottom of the final SaveAs problem. For completeness, here is the link to that post


    http://www.vbaexpress.com/forum/showthread.php?58766-Run-time-error-1004-Method-SaveAs-of-object-_Workbook-failed

    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


    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


    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.

    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


    Hello


    I'm trying to set up a macro to copy data from columns B and C in rows 2:200 of worksheet A where specific criteria in column S of that worksheet is met. The data would copy to columns A and B of worksheet B, starting in row 5, having first cleared down any data in the first 20 rows from row 5 which may have been previously populated. The data to be copied as value only, i.e. not bringing across formats from the first worksheet.


    My VBA knowledge is basic, to say the least, but I usually manage to work it out by searching forums such as this, finding answers to similar questions and modifying the code, but this one has me stumped.


    This is what I've got so far. Many thanks in advance for advice/assistance!


    Re: Date and number formats lost when workbook is closed


    Ah, I see. When I said custom I meant standard Excel custom. I have now managed to find the solution to this problem... the workbook was corrupt. This showed itself initially by the cell styles listed in the Home ribbon all changing to the same type. Stupidly I ignored this before copying the data to a new workbook, (which solved the problem), and the file fully corrupted and I lost the formulae and have just had to re-do it. Oh well!


    Thanks for your advice though - much appreciated

    Re: Date and number formats lost when workbook is closed


    Hi Krishnakumar


    Many thanks for responding but I'm not entirely sure what you mean. In effect, all custom and standard formats are lost when the workbook is open. How would I go about deleting the custom formats?


    Many thanks

    I have a simple workbook in 2007, i.e. basic formula, a few tabs, no macros. Some items are formatted to custom date format mmm-yyyy, some to standard number format no decimal places, no comma separators, and some to standard number format two decimal places, comma separator.


    Every time the file is closed the formats all revert back to General, i.e. the date formats go back to the Excel date code and the number formats lose all decimal points, etc. This doesn't happen when the file is just saved, only when it is either saved and then closed or closed and 'yes' to save is selected.


    I've never had this happen before and it's extremely frustrating to have to go through and change all these every time.


    Any clues as to how to fix this would be very much appreciated!!