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

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

Hi Ed

Try this

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


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



"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message news:[email protected]...
> 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
>
>

It isn't that hard:

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

--
Regards,
Tom Ogivly

"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> 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
>
>

Thanks, Ron. Worked great!

Ed

"Ron de Bruin" <[email protected]> wrote in message
news:[email protected]...
> Hi Ed
>
> Try this
>
> Sub test()
> ActiveSheet.Copy
> With ActiveWorkbook.Sheets(1).UsedRange
> .Value = .Value
> End With
> End Sub
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:[email protected]...
> > 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
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> It isn't that hard:
>
> Activesheet.copy
> Activesheet.cells.copy
> Activesheet.pasteSpecial xlValues
> ActiveWorkbook.SaveAs "MyUnlinkedCopy.xls"
>
> --
> Regards,
> Tom Ogivly
>
> "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> news:[email protected]...
> > 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
> >
> >
>
>

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.

--
Regards,
Tom Ogilvy



"Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > It isn't that hard:
> >
> > Activesheet.copy
> > Activesheet.cells.copy
> > Activesheet.pasteSpecial xlValues
> > ActiveWorkbook.SaveAs "MyUnlinkedCopy.xls"
> >
> > --
> > Regards,
> > Tom Ogivly
> >
> > "Ed" <ed_millis@NO_SPAM.yahoo.com> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > >
> >
> >
>
>