copy worksheets to new book without linking to original book
 

Trying to create a new book from existing book that brings over values only,
allowing me to delete any of the worksheets without disturbing the end result
on adjoining pages. I know I can individually do a paste special, values,
page by page but this will be extremely time consuming.

It seems I used to be able to just copy sheets to new book, but this is no
longer working for me.

copy worksheets to new book without linking to original book
 

If you're trying to create a duplicate workbook, why bother with copying
sheets - just make a copy of the workbook and use it? You can do the
equivalent of Edit Copy / Edit Paste Special | Values with the following VBA
code for all sheets in the workbook - just press [Alt]+[F11] and once you're
into the VBA editor, copy and paste this code into it and then [Run] it from
Tools | Macro | Macros.

Sub CarveInStone()
Dim anyWS As Worksheet
Dim anyRange As Range
For Each anyWS In ThisWorkbook.Worksheets
Set anyRange = anyWS.UsedRange
anyRange.Formula = anyRange.Value
Next
Set anyRange = Nothing
Set anyWS = Nothing
End Sub


You can then double-check using Edit | Links to see if any links have been
left in place by Excel (shouldn't be but might be - but they'd be 'ghost'
links), and simply use the [Break Links] option to get rid of them.


"Lori" wrote:

Trying to create a new book from existing book that brings over values only,
allowing me to delete any of the worksheets without disturbing the end result
on adjoining pages. I know I can individually do a paste special, values,
page by page but this will be extremely time consuming.

It seems I used to be able to just copy sheets to new book, but this is no
longer working for me.

copy worksheets to new book without linking to original book
 

Thank you. This worked great! A little scary, since I don't know anything
about writing code, which explains why I use the copy/paste.

"JLatham" wrote:

If you're trying to create a duplicate workbook, why bother with copying
sheets - just make a copy of the workbook and use it? You can do the
equivalent of Edit Copy / Edit Paste Special | Values with the following VBA
code for all sheets in the workbook - just press [Alt]+[F11] and once you're
into the VBA editor, copy and paste this code into it and then [Run] it from
Tools | Macro | Macros.

Sub CarveInStone()
Dim anyWS As Worksheet
Dim anyRange As Range
For Each anyWS In ThisWorkbook.Worksheets
Set anyRange = anyWS.UsedRange
anyRange.Formula = anyRange.Value
Next
Set anyRange = Nothing
Set anyWS = Nothing
End Sub


You can then double-check using Edit | Links to see if any links have been
left in place by Excel (shouldn't be but might be - but they'd be 'ghost'
links), and simply use the [Break Links] option to get rid of them.


"Lori" wrote:

Trying to create a new book from existing book that brings over values only,
allowing me to delete any of the worksheets without disturbing the end result
on adjoining pages. I know I can individually do a paste special, values,
page by page but this will be extremely time consuming.

It seems I used to be able to just copy sheets to new book, but this is no
longer working for me.