How to apply the vba (mcgimpsey) to all sheets

[cham.behrooz]

I am using following vba code from mcgimpsey website on my
Thisworkbook and it works fine but I like to know how to apply the
same code to Sheet 2 and 3 on my workbook. Basically I want the result
in cell B2 would show up on the same cell of Sheet 2 and 3. Anybody
can help?

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Sheet1")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber
+ 1&
End If
End With
End With
End Sub

 

[Bob Greenblatt]

I am using following vba code from mcgimpsey website on my
Thisworkbook and it works fine but I like to know how to apply the
same code to Sheet 2 and 3 on my workbook. Basically I want the result
in cell B2 would show up on the same cell of Sheet 2 and 3. Anybody
can help?

Private Sub Workbook_Open()
Const sAPPLICATION As String = "Excel"
Const sSECTION As String = "Invoice"
Const sKEY As String = "Invoice_key"
Const nDEFAULT As Long = 1&
Dim nNumber As Long

With ThisWorkbook.Sheets("Sheet1")
With .Range("B1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
With .Range("B2")
If IsEmpty(.Value) Then
nNumber = GetSetting(sAPPLICATION, sSECTION, sKEY,
nDEFAULT)
.NumberFormat = "@"
.Value = Format(nNumber, "0000")
SaveSetting sAPPLICATION, sSECTION, sKEY, nNumber
+ 1&
End If
End With
End With
End Sub

The easiest way is to put the following formula in cell B1 of sheet2 and 3:
=sheet1!b1

 

[JE McGimpsey]

The easiest way is to put the following formula in cell B1 of sheet2 and 3:
=sheet1!b1

Yup - that's what I told him via email, except that I hadn't seen the
specific cells he's using.

However, since the serial number's being put into B2, I'd suggest this
(perhaps also) be put into cell B2 of sheets 2 and 3:

=Sheet1!B2

 

[cham.behrooz]

Yup - that's what I told him via email, except that I hadn't seen the
specific cells he's using.

However, since the serial number's being put into B2, I'd suggest this
(perhaps also) be put into cell B2 of sheets 2 and 3:

   =Sheet1!B2

Thanks so much gentlemen! It did work! Sorry John yes you did tell me
but I misunderstood you just because I thought " =Sheet1!B2" was a vba
code and I tried to put it on vba module and that was my mistake..What
a rookie! Once again I appreciate it and love your code. The only
small problem I discovered is when two differenent poeple try to open
this file, the serial # stays the same because I am using this file as
shared file on network which everybody has access to. Basically each
time you open it up you must close it so that when the next person
opens it up he will get a new number. Also as a reminder, I saved this
file and "Read-Only" file. Is there a way to fix this problem?