Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Saving part of workbook (2000 SR-1)

    Hi Everyone...

    I'm hoping this is possible... <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

    I have a workbook containing 6 worksheets... The first three are all pretty (named, formatted, <img src=/S/yadda.gif border=0 alt=yadda width=15 height=15> as the end user needs it...) and the last three contain numerous sections of data that are retrieved and refreshed through database queries.... The data from that last three sheets ties in all over the place in the first three... (for example: C15 might contain "=Sheet4!J46")

    Here's what I want to do... If possible, I'd like to finish all of the updating and then save the first three sheets only to a new workbook that will be emailed to various sources... I'm automating the process for my department, and I would prefer that the new workbook not contain any links or queries... Just the data but in the same format...

    Can anyone help me? <img src=/S/please.gif border=0 alt=please width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Saving part of workbook (2000 SR-1)

    Perhaps you can use this as starting point. At the very least, you'll have to fill in the dots <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Sub ExportThreeSheets()
    Dim i As Integer
    Dim wbk As Workbook

    On Error GoTo ErrHandler

    Application.DisplayAlerts = False
    ' Set reference to workbook
    Set wbk = Workbooks("...")

    ' Replace formulas by values in first 3 sheets
    For i = 1 To 3
    With wbk.Worksheets(i).UsedRange
    .Copy
    .PasteSpecial Paste:=xlPasteValues
    End With
    Next i

    Application.CutCopyMode = False

    ' Delete last three sheets
    wbk.Worksheets(Array(4, 5, 6)).Delete

    ' Save workbook under different name
    wbk.SaveAs "..."
    ' Close workbook without saving
    wbk.Close SaveChanges:=False

    ExitHandler:
    Application.DisplayAlerts = True
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving part of workbook (2000 SR-1)

    There are several possibilitites.

    1- You could hide the worksheets that you don't want the users to see. If you go into the VB Editor and make them xlVeryHidden, then it would be very difficult for any users to ever see them.

    2- You can't just delete the sheets you don't want seen and then save those that are left. You will end up with a bunch of #REF errors. You could select all of the cells that contain references to those sheets, Copy them, then do a Paste Special and paste values back into those cells. Then you could delete the sheets you don't want them to see. If you could do this by selecting complete sheets and doing the Copy/Paste Special then it would be fairly easy. However, if you have other formulas that you want to keep working, then you would have to select only the cells containing the references to the sheets you don't want seen.

    3- You could move the worksheets you don't want seen to another workbook, and change all of the references to reference the other workbook. Then send the workbook to the users and tell them to respond No when they open the workbook and are asked if they want to update the links.
    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Jan 2003
    Location
    San Francisco Bay Area, California, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving part of workbook (2000 SR-1)

    Alexya1,
    1. Copy the worksheets: Select the three worksheets by holding down Shift and then clicking on each tab with the mouse cursor. With the mouse cursor still pointed at one of the tabs, right click - then To Book: "New workbook", put a checkmark in the "make a copy" box.
    2. Convert formulas to values: In your new workbook, (1) on each sheet, select the range with contents (Shift+Control+End), then Edit, Paste Special - Values. This will remove links to your data worksheets.
    3. Delete External Names: If you've used named cells/ranges, these names may linked also. Select Insert Name Define and examine whether any names refer to external sources. You'll need to delete all of them. I use a VBA routine to do this (sample attached). This routine will delete all names. You can also set it up to loop through all of the worksheets in the workbook.

    Hope this helps.
    Andy

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving part of workbook (2000 SR-1)

    Wow... Thanks Fellas!! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> You guys are GOOD!
    I'm sure that's plenty of help for now... I'll get to work on it and post again if I need more expert assistance...

    Have a great afternoon!

    On another note: Wohoo! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> I made it to "Lounger" !!!... I thought I'd be NewLounger for another year or two... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Saving part of workbook (2000 SR-1)

    Just another sample of code :

    Sub CopyAndMail()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    For Each ws In ActiveWorkbook.Sheets
    ws.UsedRange.Copy
    ws.UsedRange.PasteSpecial Paste:=xlPasteValues
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    With ActiveWorkbook
    .SaveAs "PathFilename.xls"
    .SendMail _
    Recipients:="List of Recipients", _
    Subject:="Attached Workbbok"
    .Close
    End With
    End Sub

    This should

    1. create a new workbook from Sheets 1,2 & 3 (you may need to change the sheeet names) ,
    2. replace any formulas with values (hence remove links),
    3. save with new file name and
    4. email the new workbook to a list of recipients.

    Andrew C

  7. #7
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Saving part of workbook (2000 SR-1)

    Do you need to send the worksheets or could you just send the output, e.g., as PDF files?
    If so, print each worksheet as PDF and send the PDF files.

Posting Permissions

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