Saving part of workbook (2000 SR-1)

Topic

New Reply

Hi Everyone…

I’m hoping this is possible…

I have a workbook containing 6 worksheets… The first three are all pretty (named, formatted, 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?

Reply | Quote

Replies

Perhaps you can use this as starting point. At the very least, you’ll have to fill in the dots

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Wow… Thanks Fellas!! 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! I made it to “Lounger” !!!… I thought I’d be NewLounger for another year or two…

Reply | Quote

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

Reply | Quote

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.

Reply | Quote