Copy spreadsheet as values

Mmmm, a VBA post. Hopefully there won’t be too many of these.

Our system has an excel interface, and many models are built on top of this interface. The issue with this is that when the spreadsheet opens, it will refresh the data that is currently in the system. Normally, this is exactly what you want, but sometimes it would be nice to create a snapshot of a model at a certain time. Copy and paste as values is how you achieve this manually, here are some functions that achieve this programatically via VBA, and automatically creating a new workbook.

Public Sub ArchiveWorkbook()
    Set NewWorkbook = CreateWorkBook(ThisWorkbook.Worksheets.Count)
    If SaveWorkbook(ThisWorkbook.FullName) Then
        CopyValuesToDestination
    End If
End Sub

Function CreateWorkBook(wsCount As Integer) As Workbook
    Dim OriginalWorksheetCount As Long
    Set NewWorkbook = Nothing
    If wsCount < 1 Or wsCount > 255 Then Exit Function
    OriginalWorksheetCount = Application.SheetsInNewWorkbook
    Application.SheetsInNewWorkbook = wsCount
    Set NewWorkbook = Workbooks.Add
    Application.SheetsInNewWorkbook = OriginalWorksheetCount
    Set CreateWorkBook = NewWorkbook
End Function

Function SaveWorkbook(originalFileName As String)
    On Error GoTo ErrHandler:
    NewWorkbook.SaveAs Filename:=originalFileName & "_staticCopy"
    SaveWorkbook = True
    Exit Function
    
ErrHandler:
    SaveWorkbook = False
End Function

Function CopyValuesToDestination()
    Dim source As Range
    Dim dest As Range
    Dim sourceWs As Worksheet
    Dim destWs As Worksheet
    Dim wsCounter As Integer
    
    For wsCounter = 1 To ThisWorkbook.Worksheets.Count
        Set sourceWs = Nothing
        Set source = Nothing
        Set dest = Nothing
        Set destWs = Nothing
        Set sourceWs = ThisWorkbook.Worksheets(wsCounter)
        Set source = sourceWs.Range("A1:DA150")
        
        source.Copy
        
        Set destWs = NewWorkbook.Worksheets(wsCounter)
        Set dest = destWs.Range("A1:DA150")
               
        dest.PasteSpecial xlPasteValues
        dest.PasteSpecial xlPasteFormats
        dest.PasteSpecial xlPasteColumnWidths       
        destWs.Name = sourceWs.Name
    
    Next wsCounter
    
End Function