Sunday, May 27, 2007

Save copy of the workbook

Sub Saving_A_Copy_Of_Workbook()

'Saves a copy of the workbook to a file but doesn't modify the open workbook in memory

Dim bAchievedTarget As Boolean

If bAchievedTarget = True Then
ActiveWorkbook.SaveCopyAs "c:\Completed\FinalCopy.xls"
End If

' This function can be used to take the copy of the file while working on it. The current file remains the same however

End Sub

See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Save RTF document as word

8 comments:

  1. Anonymous11:31 AM

    awesome! thanks

    ReplyDelete
  2. Anonymous7:37 AM

    when does this boolean ever become true? it should be false by default

    ReplyDelete
  3. Anonymous11:49 AM

    Nice!
    But I do have a question. I am using a macro toolbar to save the copy of the workbook, but is save the macro instead of my worksheet, how can I fix this problem?

    ReplyDelete
  4. If you are using something like 'ThisWorkbook.SaveAs' from the Code it will save the Macro as Thisworkbook refers to the workbook from which the code is executed.

    Either activate the workbook you want to save or use the Workbooks collection

    Workbooks("Sample.xls").SavecopyAs

    ReplyDelete
  5. Anonymous3:10 AM

    I have the following code:

    ActiveWorkbook.SaveCopyAs sArivefile

    So a copy of the file is "called" sArchivefile. Everything works nicely but if I look at the copy-file the properties of the file (in menu File, then Properties) are gone. For example the title of the document, the creator of the document and the hyperlink-base is gone. Can someone explain how this happens and how it can be solved that this information is also copied?

    ReplyDelete
  6. Anonymous8:16 AM

    wow.. ive been looking for this forever. Thanks!

    ReplyDelete
  7. Anonymous6:18 AM

    The reason your file properties have gone is because the savecopyas function doesn't allow you to define the file type. If you add in something like & ".xlsm" at the end it will work.
    Just had that problem but otherwise this all works great, thanks for your help.

    ReplyDelete
  8. Anonymous12:01 AM

    I have a dashboard, with macros. After populating the data i want to save it using a command button. I have assigned this code to the command button but its not working. Please advise. M new to vba so having a hard time. the name of my file is Australia.

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.