+ Reply to Thread
Results 1 to 13 of 13

Code to save worksheet

  1. #1
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Code to save worksheet

    Hi...I have this code to save a worksheet as another workbook but I get an error

    Please Login or Register  to view this content.
    I want the user to be able to choose a filename to save as.


    The error is on this line
    Please Login or Register  to view this content.
    This worksheet also has many macros which I want to retain when the worksheet is saved as a different workbook.

    Can anyone offer any guidance on this please? Thanks
    Last edited by fusion007; 06-21-2011 at 03:12 PM. Reason: Corrected Code Tags

  2. #2
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Code to save worksheet

    If you want to retain the macros and they are stored in modules you may be just as easy copying the whole workbook and then stripping out the stuff that you don't want.

    To prompt the user for a name to save the workbook have a look at the GetSaveAsFilename method.

    Dom
    "May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."

    Use code tags when posting your VBA code: [code] Your code here [/code]

    Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.

  3. #3
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    Quote Originally Posted by Domski View Post
    If you want to retain the macros and they are stored in modules you may be just as easy copying the whole workbook and then stripping out the stuff that you don't want.

    To prompt the user for a name to save the workbook have a look at the GetSaveAsFilename method.

    Dom

    Thanks Dom. Different users will be using this workbook and asking them to delete the sheets they don't want will be too much work for them - according to them!
    As an alternative when saving the workbook is it possible to have a code to delete all the other sheets and just keep the current worksheet?

    I'l take a look at GetSaveAsFilename method.

    Thanks

  4. #4
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    Quote Originally Posted by fusion007 View Post
    Thanks Dom. Different users will be using this workbook and asking them to delete the sheets they don't want will be too much work for them - according to them!
    As an alternative when saving the workbook is it possible to have a code to delete all the other sheets and just keep the current worksheet?

    I'l take a look at GetSaveAsFilename method.

    Thanks
    I got the GetSaveAsFilename to work

    Please Login or Register  to view this content.
    Now I need some help with a way to be able to keep the macros on the current worksheet or delete the other worksheets from the workbook when saving a copy.
    Thanks

  5. #5
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Code to save worksheet

    Please don't quote whole posts in your replies per the forum rules.

    See if this does what you want:

    Please Login or Register  to view this content.

    Dom

  6. #6
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    Ok sorry I won't quote whole posts.

    I tried the code and I do get a prompt to save the file but I get a debug error on this part of the code

    .SaveAs strFileName

  7. #7
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Code to save worksheet

    Strange, works fine for me. Can you upload a sample workbook with the code that's not working in it?

    Dom

  8. #8
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    Hello again...I've just noticed that the code has got rid of the other sheets in the original woorkbook as well which I need to keet for future use. I just want to get rid of the other worksheets in the new file that will be created.

  9. #9
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    Hi...I closed and opened the application again and this time round it worked fine! With regards to my later post about removing the extra worksheets in the original workbook - please ignore this as I didn't realise that the original workbook closed after saving the new file. I was looking at the new saved file!
    The original workbook is intact.
    Thank you so much for your help with this. I really appreciate it.

  10. #10
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Code to save worksheet

    No worries, I was a little confused but there were a couple of flaws in the code.

    This is probably a bit more robust:

    Please Login or Register  to view this content.

    Dom

  11. #11
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    Thanks Dom. I've updated the code.

    There is one thing I would like to ask you if I may. This is to do with the same workbook as this one though a different issue.
    On this workbook I have one worksheet which copies updated information on to another worksheet which has a template and then this updated template is saved as a seperate file. This is the code you have helped me with.

    On the copy side of things, when I run the macro the data starts to copy from the row I have specificed in the macro but it starts to copy from column A but I want the copy to start from column C. Can this easily be done?

    Thanks again

  12. #12
    Forum Expert Domski's Avatar
    Join Date
    12-14-2009
    Location
    A galaxy far, far away
    MS-Off Ver
    Darth Office 2010
    Posts
    3,950

    Re: Code to save worksheet

    I'm sure it's possible but as it's a different issue the general rule is to start another thread. I also think it would be useful to see a sample workbook of what you are trying to achieve.

    Dom

  13. #13
    Registered User
    Join Date
    06-20-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    91

    Re: Code to save worksheet

    I will set this thread to solved. Thanks again.

    Gor the other partI have a thread where the code I am using and the workbooks are attached. The thread is

    http://www.excelforum.com/excel-prog...reference.html

+ 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