CODEWITHSUNDEEP
excelvba
JoaMika
JoaMika

Reputation: 1827

Create workbook and copy sheets with values only

Sub values_dump()
Dim sourceWB As Workbook
Dim destWB As Workbook
Dim ws As Worksheet
Dim path As String
Dim fname As String

Application.ScreenUpdating = False

path = ThisWorkbook.path & "\_bck\"
fname = "values_" & Format(Now, "dd_mmm_yy_hh_mm_ss") & ".xlsm"

Set sourceWB = ThisWorkbook

Set destWB = Workbooks.Add
destWB.SaveAs path & fname

For Each ws In sourceWB.Worksheets

Workbooks(sourceWB).Sheets(ws).Copy after:=Workbooks(destWB).Sheets(1)

Next ws

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

I am getting an error on this line destWB.SaveAs path & fname - it says that I cannot use the ".xlsm" extension ?

In addition I would like to copy the sheets to the new workbook but only retain the values and original formatting.

My code, erroneously copies all the formulae. I do not want to destruct in any way the original workbook.

Upvotes: 1

Views: 1056

Answers (1)

user4039065
user4039065

Reputation:

You are arbitrarily tacking on a Macro-Enabled Workbook file extension (e.g. xlsm) but using Workbook.SaveAs method with the default FileFormat paramter (found in Excel Options ► Save ► Save files in this format:. In fact, it would be better to leave off the .xlsm altogether and specify the desired file format. Excel will add .xlsm if you pick the correct format. See xlFileFormat enumeration for a full list of available SaveAs file types.

If you want to revert the formulas to their values, simply make a copy of the worksheet then use .Cells = .Cells.Value. 

Sub values_dump()
    Dim sourceWB As Workbook
    Dim destWB As Workbook
    Dim ws As Worksheet
    Dim path As String
    Dim fname As String
    Dim c As long

    Application.ScreenUpdating = False

    path = ThisWorkbook.path & "\_bck\"
    fname = "values_" & Format(Now, "dd_mmm_yy_hh_mm_ss") & ".xlsm"

    Set sourceWB = ThisWorkbook
    Set destWB = Workbooks.Add
    destWB.SaveAs Filename:=path & fname, FileFormat:=xlOpenXMLWorkbookMacroEnabled 'Open XML Workbook Macro Enabled (52)

    For Each ws In sourceWB.Worksheets

        if ws.autofiltermode then ws.autofiltermode = false
        ws.Copy after:=destWB.Sheets(1)
        With destWB.Sheets(2).usedrange
            for c = 1 to .columns.count
                .columns(c).Cells = .columns(c).Cells.Value
            next c
        End With
        destWB.save

    Next ws

    Application.ScreenUpdating = True
End Sub

When you Set a workbook-type var to a Workbook Object, you can use the var directly. You seemed to be using it like it was the Workbook.Name property. The same goes for the Worksheet Object and the Worksheet .Name property.

Upvotes: 1

Related Questions