copy worksheets to new book without linking to original book

[Lori]

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.

 

[JLatham]

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]

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.

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.

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.