Move/Copy Worksheet (Excel 97-SR2)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Like this:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Hi Steve,

<>

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

<>

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…

<>

Correct.

<>

oName.Delete

.

Reply | Quote

Jan,

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

My 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

Reply | Quote

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

Reply | Quote

It “exports” all sheets that are selected, so: Yes, it will work. Just select the sheets you want included first, then run the macro.

Reply | Quote

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.)

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Regards
Peter

Reply | Quote