+ Reply to Thread
Results 1 to 7 of 7

Open workbook, copy data, save and close error

  1. #1
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Open workbook, copy data, save and close error

    Hi there,

    I hope someone may be able to help me please?

    I have a workbook (workbook A) with lots of data in.
    A second workbook (workbook B) is to be found in a location which doesn't change, opened, some sheets copied over and for it to be saved as a copy in the same location as workbook A. I have a code to do this and it seemed to work perfectly.

    However I have added a third workbook, workbook C which I want exactly the same thing to happen as with workbook B. The data copied will be slightly different but the process is the same.

    When I use the same code for workbook C I don't get a VBA error but it says the excel needs to close and about an error report - could anyone help me with the reason why please and how I can fix it?

    My current code is below. Many thanks.

    Please Login or Register  to view this content.
    Last edited by Jessica.Bush; 01-01-2012 at 12:44 PM.

  2. #2
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Open workbook, copy data, save and close error

    Possibly this line of code is the problem...
    Please Login or Register  to view this content.
    The string "newpathway" is never assigned a value, so the SaveCopyAs method is saving a duplicate file with the same name in the same directory as the original file?

  3. #3
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Open workbook, copy data, save and close error

    Hi dangelor,

    Thanks for your reply.

    Please Login or Register  to view this content.
    It worked when I only wanted workbookB fine and would make a copy and save it in the same location as workbookA from which the macro was being run... do you have any other suggestions that I could try please?

    Many thanks.

  4. #4
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Open workbook, copy data, save and close error

    Here is your code, unchanged except for my comments on possible problems.

    I ran it on my system and in only generated a VBA error on the misspelling of Sheet3 in the WorkbookC subroutine.

    About the newpathway string... if you are not changing the path to save the files in to a new location, why include it? and why use the SaveCopyAs method? As written, your code overwrites the existing files.

    One other possibility could be a corrupted workbook.

    HTH -Rich

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Open workbook, copy data, save and close error

    Hi Rich,

    Thanks so much for your help with this.

    The workbooks B and C are saved on the system as a 'template', always in the same place. The user has to save workbook A in a folder specific to the client being worked on. I want it to not overwrite the template but to save as a copy in the same folder as in A which is the workbook from where the code is run. This did work with just workbook B but not when I added workbook C.

    There may well be spelling mistakes, the original code had some confidential names and things in which I probably shouldn't publish so quickly pasted over them.

    I will look over your comments now and let you know whether it is solved or not.

    Thanks!

  6. #6
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Open workbook, copy data, save and close error

    Hi Rich,

    Well I played with names, swapping workbooks round etc. and it now works. It must have been a corrupted file as you said. I copied it all into a new workbook and used this as workbookC and suddenly it works!

    Sorry if I wasted your time but many thanks for your time and help, I do appreciate it and would have never have thought of a corrupted file if you hadn't mentioned it!

    Jessica

  7. #7
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Open workbook, copy data, save and close error

    Glad to help. Enjoy the day!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1