Copy of workbook woithout formulas.

[nickm687]

Hi,

Does anyone know how to write a macro so that when run an exact copy
of the current workbook is created but only with the values not the
formulas.

The workbook contains mroe than one sheet and includes graphs.

Thanks in advance.

Regrds
Nick

 

[Ron de Bruin]

Hi Nick

You can use VBA SaveCopyAs to make a copy and open that workbook with code

Sub Test()
'Working in 2000-2007
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim sh As Worksheet

Set wb1 = ActiveWorkbook

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Make a copy of the file/Open it
'If you want to change the file name then change only TempFileName
TempFilePath = Application.DefaultFilePath & "\"
TempFileName = "Copy of " & wb1.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
Set wb2 = Workbooks.Open(TempFilePath & TempFileName & FileExtStr)

For Each sh In wb2.Worksheets
With sh.UsedRange
.Value = .Value
End With
Next sh

wb2.Close SaveChanges:=True

MsgBox "You find the file here " & TempFilePath & TempFileName & FileExtStr

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[nickm687]

Hi Ron,

Your code works perfectly, thank you.

May i ask how would you, using the above code, hide one of the
workseets (e.g. sheet1) in the copy book?

Regards
Nick

 

[JMay]

add a line:
sheets("Sheet1").visible = False

 

[Ron de Bruin]

After you open wb2 you can add this

wb2.Sheets("Sheet1").Visible = False