Copy Sheet w/out Formula's, JUST values

E

Ebgar

I would like to copy a range of cells, to a new workbook/sheet withou
it linking the formulas back to the original spreadsheet. (Basicall
just capture the result values).

So what I would end up with is a *Static* copy of the sheet.

Here is where I am:

This basically does everything I want, except the new sheet links bac
to the old file for all the formulas.
==========================

Dim strDate As String
ActiveSheet.Copy
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.Close Fals
 
R

Ron de Bruin

Hi Ebgar

One way

Sub test()
Dim strDate As String
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False
strDate = Format(Now, "dd-mm-yy h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.Close False
End Sub
 
S

Scottmk

don't know if this helps, but I just recorded a macro where I copied
"paste values" to a new sheet. The result :

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 7/20/2004 by Scott
'

'
Range("C8:C16").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
End Sub

I think that the key to your problem is the Paste value
 
F

Frank Kabel

Hi
try
Dim strDate As String
ActiveSheet.Copy
with usedrange
..value=.value
end with

strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-
mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.Close False
 
Top