Copy Sheet w/out Formula's, JUST values
 

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

--
Message posted from http://www.ExcelForum.com

Copy Sheet w/out Formula's, JUST values
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ebgar " wrote in message ...
I would like to copy a range of cells, to a new workbook/sheet without
it linking the formulas back to the original spreadsheet. (Basically
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 back
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 False


---
Message posted from http://www.ExcelForum.com/

Copy Sheet w/out Formula's, JUST values
 

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

--
Message posted from http://www.ExcelForum.com

Copy Sheet w/out Formula's, JUST values
 

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


-----Original Message-----
I would like to copy a range of cells, to a new
workbook/sheet without
it linking the formulas back to the original spreadsheet.
(Basically
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 back
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 False


---
Message posted from http://www.ExcelForum.com/

.

Copy Sheet w/out Formula's, JUST values
 

That worked perfectly!

thank you very much for the quick response

--
Message posted from http://www.ExcelForum.com