Okay this is what I have based on the earlier advice:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sheet1").Range("G2").Value =
Worksheets("Sheet1").Range("G2").Value + 1

End Sub

Which gives me a runtime error 9 that says subscript out of range

The advice in the last post:
..Value=.Value+1

"Peter T" wrote:

"Jeff Johnson" wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
"phale" wrote in message
...
I tried following your directions and typed in the following code:

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

Worksheet("Receivingtemplate2.xlt").Range("G2").Va lue=Worksheets("Receiving
template.xlt").Range("G2
").Value + 1


Private Sub Workbook_Open()

End Sub

And I am getting a syntax error. Can you tell from this what I am doing
wrong?

Is that your EXACT code (i.e., did you copy and paste)? Because if so, the
first "Worksheets" is missing the "s" at the end.

Also, you appear to be trying to use the name of the WORKBOOK (i.e., the
file name) as an argument to the Worksheets() function instead of the name
of the SHEET as I told you. The workbook itself will already be open; you
don't need to reference it.


Just to add -
In addition to the missing "s" after Worksheet* you (OP) have another typo
for the sheet name (strange to name a sheet like a workbook-template name)

"Receivingtemplate2.xlt" vs "Receivingtemplate.xlt"

Sort out you spellings, then try something like this

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

With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With

End Sub

Obviously change "Sheet1" and "A1" as required.

Regards,
Peter T