Visual Basic BeforeSave Sub 1

[Clyde38]

I have included an automatic save subroutine using the Private Sub Workbook_BeforSave.... funciton in the VBA editor. I am using it to save to a particular path and file name. This works fine until I add a new worksheet to the workbook. All other additions or changes to the existing workbook will save automatically when I save the the workbook. What's up? See below..
I do not do many VBA programs. This is a variation of an Excel tip that I read.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim rngPullName As Range
Dim strSavePath As String
Application.DisplayAlerts = False
Set rngPullName = ThisWorkbook.Sheets(5).Range("A4")
strSavePath = "C:\Spreadsheets\myfilename\"

ActiveWorkbook.SaveAs Filename:=strSavePath & rngPullName & ".xls"
If ThisWorkbook.Saved = True Then
Cancel = True
End If
Application.DisplayAlerts = True

End Sub

 

[johnwm]

Suspect that this may be your culprit:

Set rngPullName = ThisWorkbook.Sheets(5).Range("A4")

If your new sheet shows on the tabs before position 5 then rngPullName may well be empty. Use the sheet name instead

Set rngPullName = ThisWorkbook.Sheets("sheet5").Range("A4")

substituting the actual sheetname of course

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk

 

[Clyde38]

johnwm,
Exactly! Thanks alot!

 

[johnwm]

Thanks - You're welcome!

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

Steam Engine enthusiasts:

http://www.essexsteam.co.uk