BeforeSave question
 

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?


--
RPIJG
------------------------------------------------------------------------
RPIJG's Profile: http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084

BeforeSave question
 

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" wrote in
message ...

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?


--
RPIJG
------------------------------------------------------------------------
RPIJG's Profile:
http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084

BeforeSave question
 

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.


--
RPIJG
------------------------------------------------------------------------
RPIJG's Profile: http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084

BeforeSave question
 

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)


"RPIJG" wrote in
message ...

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.


--
RPIJG
------------------------------------------------------------------------
RPIJG's Profile:
http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084

BeforeSave question
 

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 wrote:
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.

BeforeSave question
 

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
------------------------------------------------------------------------
RPIJG's Profile: http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084

BeforeSave question
 

anyone? I'm taking any ideas here.


--
RPIJG
------------------------------------------------------------------------
RPIJG's Profile: http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084

BeforeSave question
 

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)


"RPIJG" wrote in
message ...

anyone? I'm taking any ideas here.


--
RPIJG
------------------------------------------------------------------------
RPIJG's Profile:
http://www.excelforum.com/member.php...fo&userid=9285
View this thread: http://www.excelforum.com/showthread...hreadid=484084