Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving worksheets to another book - 2nd book will not appear | Excel Discussion (Misc queries) | |||
Macro to re-protect sheet w/ original password after pasting valuesin new book | Excel Worksheet Functions | |||
Linking multiple worksheets in a book | Excel Worksheet Functions | |||
to disconnect a destination book from a source book | Excel Discussion (Misc queries) | |||
linking a worksheet to one in another book | Excel Discussion (Misc queries) |