Don't change "jenny" to your worksheet name.

"jenny" is the custom property you are adding to File Properties.


Gord Dibben MS Excel MVP

On Fri, 17 Aug 2007 08:56:00 -0700, Jennifer-Houston, TX
wrote:

Thanks Gary for your input but it still does not work. I changed "jenny" to
my worksheet name and now I get an error. What am I doing wrong?

"Gary''s Student" wrote:

O.K.


This macro will create a custom property that will hold the counter:

Sub firstone()
Dim dp As DocumentProperties
Set dp = ThisWorkbook.CustomDocumentProperties
dp.Add Name:="jenny", _
LinkToContent:=False, _
Type:=msoPropertyTypeNumber, _
Value:=1
End Sub

and then the print sub:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
n = ThisWorkbook.CustomDocumentProperties("jenny").Val ue
n = n + 1
ActiveSheet.PageSetup.RightHeader = n
ThisWorkbook.CustomDocumentProperties("jenny").Val ue = n
End If
End Sub

This does not use a cell, it uses this "special" property or variable that
is associated with the file.
--
Gary''s Student - gsnu200737


"Jennifer-Houston, TX" wrote:

Thanks for the help but I don't want it to use any cells from the worksheet.
I want it to be a seperate number all together.
Thanks Jennifer

"Gary''s Student" wrote:

You are already 50% there.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
ActiveSheet.Range("K1").Value = ActiveSheet.Range("K1").Value + 1
ActiveSheet.PageSetup.RightHeader = ActiveSheet.Range("K1").Value
End If
End Sub

--
Gary''s Student - gsnu200737


"Jennifer-Houston, TX" wrote:

Does anyone know the code to put a number in the right header and have the
number change everytime you print? This number has nothing to do with the
worksheet itself, it is a totally different number.

This is what I am using to do the regular printing in the spreadsheet and
would like it to be changed for the right header only no cell reference:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
ActiveSheet.Range("K1").Value = ActiveSheet.Range("K1").Value + 1
End If
End Sub

Thanks sorry for the double post but I haven't received an answer to help me.
Jennifer