BeforeSave question

[RPIJG]

I have a template that has a TON of worksheets in it, only a few of
which are visible while a user is working. Is there a code I can use
in the BeforeSave module so that when the user goes to save the
workbook (NOT THE TEMPLATE) they only save the visible sheets so that I
can keep the file size down?

 

[Bob Phillips]

This might work for you.

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Dim i As Long
If ThisWorkbook.FileFormat = xlTemplate Then
On Error GoTo wb_exit
Application.EnableEvents = False
Application.DisplayAlerts = False
Cancel = True
For i = Worksheets.Count To 2 Step -1
If Worksheets(i).Visible <> xlSheetVisible Then
Worksheets(i).Delete
End If
End If
'process last sheet
If Worksheets(1).visibles <> xlSheetVisible Then
If Worksheets.Count > 1 Then
Worksheets(1).Delete
Else
Worksheets.Visible = True
End If
End If
End If

If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then
ThisWorkbook.SaveAs sFile
End If
Else
ThisWorkbook.Save
End If

wb_exit:
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[RPIJG]

At first it gave me a bunch of here and there about there being an End
If without an If, and now it's saying I've got a For without a Next,
but I don't see any For in your code.

 

[Bob Phillips]

It's not this line is it, which has wrapped-around in the NG

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

should be just one line.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Rowan Drummond]

I think it's the second End If which should have been Next i

<snip>
For i = Worksheets.Count To 2 Step -1
If Worksheets(i).Visible <> xlSheetVisible Then
Worksheets(i).Delete
End If
Next i '<<< Changed
<snip>

Hope this helps
Rowan

 

[RPIJG]

So this code seems to be doing some pretty weird stuff, when I'm inside
the template, and I click Save, it saves, at least from what I can tell
I think... If I use save as, it asks me to open something, which I'm not
sure why, and then if I hit cancel then it tells me to save as, maybe
I'm better off not trying to do all of this, it seems very confusing to
me.

 

[RPIJG]

anyone? I'm taking any ideas here.

 

[Bob Phillips]

A couple of errors in the code. Try this version

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim sFile
Dim i As Long
Stop
Application.EnableEvents = False
Application.DisplayAlerts = False
If ThisWorkbook.FileFormat <> xlTemplate Then
On Error GoTo wb_exit
Cancel = True
For i = Worksheets.Count To 2 Step -1
If Worksheets(i).Visible <> xlSheetVisible Then
Worksheets(i).Delete
End If
Next i
'process last sheet
If Worksheets(1).Visible <> xlSheetVisible Then
If Worksheets.Count > 1 Then
Worksheets(1).Delete
Else
Worksheets.Visible = True
End If
End If
End If

If SaveAsUI Then
sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
If sFile <> False Then
ThisWorkbook.SaveAs sFile
End If
Else
ThisWorkbook.Save
End If

wb_exit:
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)