Automatic Page Numbering

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

I have a total of 8 different sheets in one workbook. I
have most of these hidden on a daily basis. I unhide these
sheets in different order daily and I would like to have a
formula that numbers cell A8 (because A7 = "Page") on each
unhidden sheet in chronological order.
 
Hi Troy

another option would be to check out the View / Report Manager (was included
in 2000 as an "add-in", for 2002 you need to d/l it a microsoft's site and i
have no idea about 2003) ... you could specify what sheets to print, in what
order and have continuous page numbering across it.

you could also save each "report" for use at a later stage - so if there is
some logic to what pages on what days you can incorporate this into the
report.

Cheers
JulieD
 
I'm not looking for page numbering on print outs, I'm
looking for Page Numbering in a particular cell block.
 
Hi Troy

okay, how about this code

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

Dim ws As Worksheet
Dim i As Integer

i = 0
For Each ws In Worksheets
If ws.Visible Then
i = i + 1
ws.Range("A8") = i
End If
Next ws
End Sub


it will loop through all visible sheets putting the "page" number in cell A8
when you SAVE the workbook after hiding / displaying the sheets.

Hope this helps
Cheers
JulieD
 
Is this a Macro? I know nothing of Macros.
-----Original Message-----
Hi Troy

okay, how about this code

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

Dim ws As Worksheet
Dim i As Integer

i = 0
For Each ws In Worksheets
If ws.Visible Then
i = i + 1
ws.Range("A8") = i
End If
Next ws
End Sub


it will loop through all visible sheets putting the "page" number in cell A8
when you SAVE the workbook after hiding / displaying the sheets.

Hope this helps
Cheers
JulieD







.
Click to expand...
 
Hi Troy

yes it is a macro, as AFAIK there is no worksheet function that will return
the index number of a visible sheet,
to use the macro,
right mouse click on any of your sheet tabs
and choose view code
this will open up the VB Editor
on the left hand side you should see the name of your workbook in bold (and
brackets) and under that a thing called "ThisWorkbook"
double click on ThisWorkbook and a white piece of paper should appear on the
right hand side of the screen
cut & paste the code into this white sheet of paper from
before
Sub Worksheet
right to the end of End sub

now choose from the menu
Debug / Compile VBA Project
this will show you any errors - none should come up but its a good test,
now close the VB Editor using the top X
and save your workbook - the code should run just before it performs the
save.

You might like to check it out in a blank workbook with a couple of sheets
hidden before putting it in your workbook.
It is also a good idea before testing out any code etc to make a backup copy
of your workbook - just in case

let us know how you go.

Cheers
JulieD
 
I think I am doing something wrong because nothing happens

----- JulieD wrote: ----

Hi Tro

yes it is a macro, as AFAIK there is no worksheet function that will retur
the index number of a visible sheet
to use the macro
right mouse click on any of your sheet tab
and choose view cod
this will open up the VB Edito
on the left hand side you should see the name of your workbook in bold (an
brackets) and under that a thing called "ThisWorkbook
double click on ThisWorkbook and a white piece of paper should appear on th
right hand side of the scree
cut & paste the code into this white sheet of paper fro
befor
Sub Workshee
right to the end of End su

now choose from the men
Debug / Compile VBA Projec
this will show you any errors - none should come up but its a good test
now close the VB Editor using the top
and save your workbook - the code should run just before it performs th
save

You might like to check it out in a blank workbook with a couple of sheet
hidden before putting it in your workbook
It is also a good idea before testing out any code etc to make a backup cop
of your workbook - just in cas

let us know how you go

Cheer
Julie



Troy said:
Is this a Macro? I know nothing of Macros
Click to expand...
 
one thing i did forget to say was that you will need to set your security
settings to MEDIUM - tools / macros / security - if you could check that -
if nothing still happens when you save the workbook, please feel free to
remove the "remove this" out of my email address and post me a blank test
workbook with the macro pasted into it - so i can see what is going on.

Cheers
JulieD
 
Hi Troy

it doesn't when i run it ... tell you what, email me the code in a blank
workbook with a couple of sheets hidden and i'll have a look at what's going
on.

Cheers
JulieD
 
Back
Top