Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 272
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 272
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Moving worksheets to another book - 2nd book will not appear jb0100 Excel Discussion (Misc queries) 2 March 18th 09 04:09 PM
Macro to re-protect sheet w/ original password after pasting valuesin new book [email protected] Excel Worksheet Functions 1 June 4th 08 04:27 PM
Linking multiple worksheets in a book outlawpolyester Excel Worksheet Functions 3 February 18th 08 08:16 AM
to disconnect a destination book from a source book officegirl Excel Discussion (Misc queries) 4 December 10th 07 10:28 PM
linking a worksheet to one in another book Alec Holliday Excel Discussion (Misc queries) 1 January 2nd 06 03:06 AM


All times are GMT +1. The time now is 12:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"