Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy of workbook woithout formulas.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy of workbook woithout formulas.
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nickm687" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy of workbook woithout formulas.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy of workbook woithout formulas.
add a line:
sheets("Sheet1").visible = False "nickm687" wrote in message ps.com: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy of workbook woithout formulas.
After you open wb2 you can add this
wb2.Sheets("Sheet1").Visible = False -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "nickm687" wrote in message ps.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy formulas to another workbook | Excel Discussion (Misc queries) | |||
Copy formulas from one workbook to another | Excel Worksheet Functions | |||
COpy formulas from one workbook to another workbook | Excel Discussion (Misc queries) | |||
copy formulas to another workbook | Excel Discussion (Misc queries) | |||
How to copy formulas from one workbook to another | Excel Discussion (Misc queries) |