saving static copy of workbook

kdt2006

Board Regular
Joined
Apr 6, 2006
Messages
88
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

MBinMTL

New Member
Joined
Nov 6, 2006
Messages
29
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

New Member
Joined
Jul 31, 2007
Messages
12
This is code off of one of my workbooks that i use.



Code:
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.
 

Forum statistics

Threads
1,181,375
Messages
5,929,585
Members
436,681
Latest member
natalie123vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top