saving static copy of workbook

[kdt2006]

Hi

I was wondering if there was a way of saving a static copy of a workbook. I currently have a workbook with many formulas, particularly using the now() function. I would like to have the ability to save the worksheet as a snapshot of when it was saved for future comparisons.

Thanks

 

[MBinMTL]

I run monthly forecasts and always want to spit out a static version of the results. My solution was to use a macro that copies and pastes the 3 results worksheets into 3 worksheets with all the formatting but no data, and pastes values only. I then copy those 3 worksheets into a new workbook. If your workbook is not too big you can do this manually.

 

[bnpaco]

This is code off of one of my workbooks that i use.

Dim Bookname As String
    Dim OriginalBook As String
    Dim TimeStamp As String
    Dim NewBook As String
    Dim x As Integer
    OriginalBook = ActiveWorkbook.Name

    TimeStamp = Now
    Fileout = "FilterTurbidity" & DatePart("m", TimeStamp) & DatePart("d", TimeStamp) & DatePart("yyyy", TimeStamp)
    
    OriginalBook = ActiveWorkbook.Name
    Bookname = "\\wtpserver\hmi\Filter Reports Catalog\" & Fileout & ".xls"         'creates a unique filename based on the date
    Workbooks.Add                                                                   'creates a temporary workbook to hold copied data
    NewBook = ActiveWorkbook.Name
    Workbooks(OriginalBook).Activate                                                'sets original workbook as the target
    ActiveWorkbook.RefreshAll                                                       'refreshes all sql queries 
    Cells.Copy                                                                      'copies all data
    Workbooks(NewBook).Activate                                                     'sets the temporary workbook as the target
    Cells.PasteSpecial xlPasteColumnWidths                                          'copies column widths
    Cells.PasteSpecial xlPasteValuesAndNumberFormats                                'replaces with values only
    Range("A1:O1").Select                                               'this section is for making static columns in bold font
    Selection.Font.Bold = True
     Range("A1:O1").Select
    With Selection.Font
        .Name = "Arial"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Application.CutCopyMode = False
    ActiveWorkbook.Close True, Bookname                                             'closes both the new workbook and the original

I'm sure with some tweaking you can use this code or get an idea of how its done.