Hi all I want to get page numbers into this macro if anyone can help

Day1 worksheet checks range ak5:ak40 if that is blank it omits page 5
from first array print if it is not blank it prints on second array.
I just cant work out how to put page numbers in here..
The page numbers on Day1 worksheet are 1 - 3 - 5 - 6

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("Day1").Range( "AK5:AK40")) = 36
Then
Sheets(Array("1 To:=1,", "3, To:=3,", "6, To:=6,")).Select
Else
Sheets(Array("From:=1, To:=1,", "From:=3, To:=3", "From:=5,
To:=5,", "From:=6, To:=6,")).Select
End If
Sheets("1").Activate
ActiveWindow.Selectedpages.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

Ta for any help

On 19 Aug., 08:07, pano wrote:
Hi all I want to get page numbers into this macro if anyone can help

Day1 worksheet checks range ak5:ak40 if that is blank it omits page 5
from first array print *if it is not blank it prints on second array.
I just cant work out how to put page numbers in here..
*The page numbers on Day1 worksheet are 1 - 3 - 5 - 6

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("Day1").Range( "AK5:AK40")) = 36
Then
* * Sheets(Array("1 To:=1,", "3, To:=3,", "6, To:=6,")).Select
Else
* * Sheets(Array("From:=1, To:=1,", "From:=3, To:=3", "From:=5,
To:=5,", "From:=6, To:=6,")).Select
End If
Sheets("1").Activate
ActiveWindow.Selectedpages.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

Ta for any help

hi pano, you can use PageSetup for the page numbers, eg

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank( _
Sheets("Day1").Range("AK5:AK40")) = 36 Then
arr = Array(1, 3, 6)
Else
arr = Array(1, 3, 5, 6)
End If
For Each sh In Sheets(arr)
sh.PageSetup.CenterHeader = "page " & sh.Index
sh.PrintOut Copies:=1, Collate:=True
Next
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

different positions are possible: left/center/rightheader and footer.

stefan

Stefan this is ok, but it prints worksheets 1,3,6 etc I want it to
print pages 1,3,6 on worksheet Day1

Any help appreciated...

Thanks Stephen

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank( _
Sheets("Day1").Range("AK5:AK40")) = 36 Then
arr = Array(1, 3, 6)
Else
arr = Array(1, 3, 5, 6)
End If
For Each sh In Sheets(arr)
sh.PageSetup.CenterHeader = "page " & sh.Index
sh.PrintOut Copies:=1, Collate:=True
Next
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

different positions are possible: left/center/rightheader and footer.

stefan

On Aug 19, 9:16*pm, pano wrote:
Stefan this is ok, but it prints worksheets 1,3,6 etc I want it to
print pages 1,3,6 on worksheet Day1

Any help appreciated...

Thanks Stephen

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank( _
Sheets("Day1").Range("AK5:AK40")) = 36 Then
arr = Array(1, 3, 6)
Else
arr = Array(1, 3, 5, 6)
End If
For Each sh In Sheets(arr)
sh.PageSetup.CenterHeader = "page " & sh.Index
sh.PrintOut Copies:=1, Collate:=True
Next
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

different positions are possible: left/center/rightheader and footer.

stefan

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("Day1").Range( "AK5:AK40"))
= 36 Then
arr = Array(1, 3, 6)
Else
arr = Array(1, 3, 5, 6)
End If
For sh = LBound(arr) To UBound(arr)
Sheets("Day1").PageSetup.CenterHeader = "page " & sh
Sheets("Day1").PrintOut From:=arr(sh), to:=arr(sh), Copies:=1,
Collate:=True
Next
Application.ScreenUpdating = True
Sheets("PrintMenu").Select
End Sub
Problem Solved thanks to PHIL

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("Day1").Range( "AK5:AK40"))
= 36 Then
arr = Array(1, 3, 6)
Else
arr = Array(1, 3, 5, 6)
End If
For sh = LBound(arr) To UBound(arr)
Sheets("Day1").PageSetup.CenterHeader = "page " & sh
Sheets("Day1").PrintOut From:=arr(sh), to:=arr(sh), Copies:=1,
Collate:=True
Next
Application.ScreenUpdating = True
Sheets("PrintMenu").Select
End Sub

Hi pano,

I have looked at this post several times and I am still not sure what you
are trying to achieve. Are you trying to select the specific pages for
printing or what?

Perhaps Ron de Bruin's code might give you a hint on how to select the print
pages. I was initially confused with VPageBreaks and HPageBreaks. It appears
from my testing that VPageBreaks are actually across the worksheet and
HpageBreaks are down the worksheet and on careful study of the Help I think
that is because a VPageBreak is the vertical line at the left of a cell and
hense breaks across the page and HPageBreaks are the horizontal line at top
of cell and hense breaks down the page but perhaps someone reading this can
confirm this for me.

If my assumption in my first paragraph is not what you want to do then
perhaps you can explain it some more.

http://www.rondebruin.nl/hpagebreaks.htm


--
Regards,

OssieMac


"pano" wrote:

Hi all I want to get page numbers into this macro if anyone can help

Day1 worksheet checks range ak5:ak40 if that is blank it omits page 5
from first array print if it is not blank it prints on second array.
I just cant work out how to put page numbers in here..
The page numbers on Day1 worksheet are 1 - 3 - 5 - 6

Sub Front1()
'Day 1
Application.ScreenUpdating = False
If WorksheetFunction.CountBlank(Sheets("Day1").Range( "AK5:AK40")) = 36
Then
Sheets(Array("1 To:=1,", "3, To:=3,", "6, To:=6,")).Select
Else
Sheets(Array("From:=1, To:=1,", "From:=3, To:=3", "From:=5,
To:=5,", "From:=6, To:=6,")).Select
End If
Sheets("1").Activate
ActiveWindow.Selectedpages.PrintOut Copies:=1, Collate:=True
Sheets("PrintMenu").Select
Application.ScreenUpdating = True
End Sub

Ta for any help