Save sheet as new file w/o refs to original?

[Ed]

Is it possible to save a sheet as a separate new file with a single command
and carry over everything EXCEPT references to the original file? I tried
it, but instead of values only I get formulas with references back to the
original workbook. Or will I need to open a new book and do a series of
Paste Specials (values, column widths, formats, etc.)?

Ed

 

[Ron de Bruin]

Hi Ed

Try this

Sub test()
ActiveSheet.Copy
With ActiveWorkbook.Sheets(1).UsedRange
.Value = .Value
End With
End Sub

 

[Tom Ogilvy]

It isn't that hard:

Activesheet.copy
Activesheet.cells.copy
Activesheet.pasteSpecial xlValues
ActiveWorkbook.SaveAs "MyUnlinkedCopy.xls"

 

[Ed]

Thanks, Ron. Worked great!

Ed

Hi Ed

Try this

Sub test()
ActiveSheet.Copy
With ActiveWorkbook.Sheets(1).UsedRange
.Value = .Value
End With
End Sub

 

[Ed]

Thanks, Tom. I had a problem, though: run-time error 1004 - "PasteSpecial
method of Worksheet failed". Help said the xlValues was only available to
the Range object, so I changed ActiveSheet to ActiveSheet.Range("A1") and it
worked okay. Is this just a difference, perhaps, in versions (I'm using
XL2000 with Win2000)? Or did I do something wrong?

Ed

 

[Tom Ogilvy]

No. Its called a typo:

Activesheet.copy
Activesheet.cells.copy
Activesheet.cells.pasteSpecial xlValues
ActiveWorkbook.SaveAs "MyUnlinkedCopy.xls"

although range("A1") would work as well.