Pages Count; Plan B

[Rookie 1st class]

I added this to Sheet1(Cover)
Range(A1) =SUM(1,'TUS 1'!A1,'TUS 2'!A1,'TUS 3'!A1,'TUS 4'!A1) ' Format
Custom ;;;
I named Sheet1,Range(A1) "MyPages"

Sheet2(TUS 1) through Sheet5(TUS4) Adjusts Range(A1) from 0 through 6 on
each Sheet depending on number of thermocouples and duration of test .'
Format Custom ;;;

I've added this code to each sheet
ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of
&N" 'Page portion has always been correct.

I need your help to change "&N" (Pages) in the header formula to "&MyPages"
' "MyPages" does not input numerical value of Sheet1,Range(A1).

If I need Const, Dim, or other code I need to know where to put it (Module
or This Workbook) and how it must read. You have MOST helpful and I really
appreciate your help. I think this corrects the last hiccup in this template.

If you have suggestions to simplify the templates PLEASE clue me in. THANKS
A TON Lou.

 

[Tom Ogilvy]

N = Worksheets("Sheet1").Range("MyPages").Value
ActiveSheet.PageSetup.RightHeader = _
"&""Arial Narrow,Regular""&8Page &P of " & N

 

[Rookie 1st class]

Aw Sh--ucks

header reads "Page 1 of N"

Range(A1) count is right, tried formats general, number with zero decimals,
and text; always diplays "{of N".
I thought the space between & and N was typo but without space it ignores
Range(A1).
Tried Dim N as Integer; got reply "this is redundant dummy" same for Dim as
String.
Tried replacing "N" with "X" no better
Tried ""N"" got {of "N". I thought this was simple; WRONG.

<Assumes lotus position> OOM OOM

 

[Rookie 1st class]

YOU ROCK!!! Thanks again Tom I owe you a HUGE favor.
<Awards several hundred attaboys>... Lou

 

[Tom Ogilvy]

Sub abc()
N = Worksheets("Sheet1").Range("MyPages").Value
ActiveSheet.PageSetup.RightHeader = _
"&""Arial Narrow,Regular""&8Page &P of " & N

End Sub


worked fine for me. The only way I could see you getting 1 of N would be
ActiveSheet.PageSetup.RightHeader = _
"&""Arial Narrow,Regular""&8Page &P of " & "N"

of if cell Mypages contains the Letter N.

 

[Rookie 1st class]

Your formula works!
My formula was already there (Macro Recorder) and appeared to be the same
HOWEVER, the N was encased in [] or straight lines.

Please learn me something what are they "[]", how, and when should are they
used?

Adds a few thousand attaboys for my oops and your response.
Lou

 

[Tom Ogilvy]

I have never seen the [] in a recording of pagesetup.

Normally [] is a shortcut for evaluate

set rng = [Sheet1!A1:A10]

or
set rng = Evaluate("Sheet1!A1:A10")

set rng = Evaluate("Sheet1!A1:A10")
? rng.Address
$A$1:$A$10

set rng1 = [Sheet1!A1:A20]
? rng1.address
$A$1:$A$20

But I don't know why you are getting it.

--
Regards,
Tom Ogilvy

Your formula works!
My formula was already there (Macro Recorder) and appeared to be the same
HOWEVER, the N was encased in [] or straight lines.

Please learn me something what are they "[]", how, and when should are they
used?

Adds a few thousand attaboys for my oops and your response.
Lou

Sub abc()
N = Worksheets("Sheet1").Range("MyPages").Value
ActiveSheet.PageSetup.RightHeader = _
"&""Arial Narrow,Regular""&8Page &P of " & N

End Sub


worked fine for me. The only way I could see you getting 1 of N would be
ActiveSheet.PageSetup.RightHeader = _
"&""Arial Narrow,Regular""&8Page &P of " & "N"

of if cell Mypages contains the Letter N.

--
Regards,
Tom Ogilvy


Dim
as through
6 on test
..' Narrow,Regular""&8Page
&P of it
(Module and I
really