Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Move/Copy Worksheet (Excel 97-SR2)

    What is the most expedient way to create a copy of worksheet to a new book? In doing so, can you remove all the links to the original/parent book?

    Issue... I have a large spreadheet model (sigh!), with 3 report worksheets within it. Periodically others may need to see 1 or more of those reports. It occurs to me that the simplist thing to do is to set up your report and then copy the relevent worksheet to a new book. This will of course result in external links in the new workbook, also all the formulae(sp?) will be copied. Anyway of avoiding that? In effect what is needed is a copy and paste values, formats, row/column sizes.

    Regards
    Peter

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Simple solution.
    Copy the worksheet in the current workbook. (right click on the sheet name - move or copy, make sure you check "create a copy") The name will be "sheetname"(2)

    Select all on this new sheet (ctrl-a), copy (ctrl-c) and edit - paste special -click values

    Move this sheet to a new book (right click on the sheet name - move or copy, DO NOT check "create a copy", to book = "(new book)")
    Save this new workbook.

    One thing to watch for: if you still get "links", check the defined names, (insert name - define), you should be able to delete all the names if there are any.

    If you do this a lot a simple macro could be created. to do this to the selected sheet.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Like this:

    <pre>Sub CopyOut()
    Dim oNewBook As Workbook
    Dim oSheet As Object
    ' Copy selected sheets into a new book
    ActiveWindow.SelectedSheets.Copy
    ' set an object to that book
    Set oNewBook = ActiveWorkbook
    ' scan its sheets to change formulas to values
    For Each oSheet In oNewBook.Sheets
    If TypeName(oSheet) = "Worksheet" Then
    oSheet.Cells.Copy
    oSheet.[a1].PasteSpecial xlValues
    Application.CutCopyMode = False
    End If
    Next
    'save the copy
    oNewBook.SaveAs Filename:="C:WINDOWSTEMPYourFielName.xls"
    ' Close the copy
    oNewBook.Close False
    ' Release memory
    Set oNewBook=Nothing
    Set oSHeet=Nothing
    End Sub</pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Thanks to both. Quick and dirty will do for now... The coded solution is more elegant especially if never send the whole model via e-mail/to anyone external.

    Regards
    Peter

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Hi Steve,

    <<You are making it way too easy on the posters!>>

    <vbg>. You are correct of course. Shows I had to much time on my hands today.

    <<You need to let them do some of the coding by themselves so they can learn to use the recorder and modify the code>>

    Although the recorder does have its merits, you know it produces notoriously bad code, that can be quite misleading for beginners (and for the more experienced as well...). But it's as good a place to start your VBA experience as any...

    <<Sometimes there is problems with named ranges, carrying over and causing "links">>

    Correct.

    <<wname.delete>>

    oName.Delete

    <g>.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    You are making it way too easy on the posters!

    You need to let them do some of the coding by themselves so they can learn to use the recorder and modify the code. I thought the hints/suggestions should be enough for them to get started, and they would ask more questions later if they needed to.

    Sometimes there is problems with named ranges, carrying over and causing "links", You might want to include in the code:

    Dim oName as Name
    For each oName in oNewBook.names
    oname.delete
    Next


    Steve

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Jan,

    Thanks for catching the typo. I edited the post to correct.

    Damn fingers they don't always seems to do what I want them to do!. They are like computers in that respect, they do what they are told, not necessarily what you want them do do!

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    If you don't want the 'copy' to be editable (sometimes this is dead handy!), an easy way is to select the range you want, copy, open a new workbook, and copy it as a picture.
    This is a quite well-kept secret.
    Do it by mousing to edit while holding down shift key, then choose paste as picture or I think there's a link option (which you prob don't want.)

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Ooooh. Now that would be smart, if only I could figure out how? It's early here after a late one involving much joy and merriment. So, apologies if I'm being a thicket. Can you explain slowly which buttons to press?

    Regards
    Peter

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Select the cells, press (and hold) your shift key, choose Edit from the menu. You'll see some entries have changed, among which "Copy Picture".
    Now go to your new sheet and press paste.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    oooh That IS sexy!

    Doesn't work of course... Seems to be limited to one 'screen' of info? The test spreadsheet I'm using has 700+ rows. (It's a MS Project import).

    Still, nice technnique for single page where you don't want people to edit info.

    Regards
    Peter

  12. #12
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Peter,

    Here's an alternative approach.
    1) Open your workbook as read-only.
    2) Copy and Paste-Values over the sheets you want to distribute.
    3) Delete unwanted sheets (Can be done as a group)
    4) Do a Save As

    Ken

    Ken

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    Jan,
    Will this work if I want to send a whole file/workbook that contains several worksheets?
    Jeff

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    It "exports" all sheets that are selected, so: Yes, it will work. Just select the sheets you want included first, then run the macro.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  15. #15
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move/Copy Worksheet (Excel 97-SR2)

    That's a thought! Will give it a try!

    Regards
    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •