Page Numbering -- using ODD or EVEn #s ONLY

[Kabeer456]

Greetings all-

I've been sorting through all kinds of message boards and other help sites in search of an answer. I have 2 worksheets representing a left page and a right page of a logbook. They are on separate worksheets because they have different column & row dimensions and I was unable to find a way to make them work on the same ws.

I would love to be able to label all of the pages on Sheet1 in the header using odd numbers only (1,3,5,7, etc.) AND all of the pages on Sheet2 in the header using even numbers only (2,4,6,8, etc.).

I've thought about labeling all the pages sequentially on each sheet and printing only the odds or evens respectively (God knows there are a million posts on printing this way), but I am trying to make it easy for everyone else in my office.

Any insight would be helpful...even if is as simple as "That's impossible." I know a tiny bit about macros and I used to know some VB, but I'm not proficient with writing functions using VBA in Excel so please support any jargon with some examples.

Thanks to all for taking the time to help!!

-Brian

 

[Fazza]

hi, Brian

What about using some code that copies the information from the two worksheets and combines it on one temporary worksheet: then print from this temporoary worksheet, which would set up to fit on two pages wide so that data from successive source worksheets will print as desired? Delete the temporary worksheet when the printing is done.

regards

 

[Kabeer456]

hi, Brian

What about using some code that copies the information from the two worksheets and combines it on one temporary worksheet: then print from this temporoary worksheet, which would set up to fit on two pages wide...

Would this allow me to copy different size sheets and combine them? if I could put then on the same ws now, I would love to but the left and right pages are completely different regarding row and column sizes.

thanks.

-Brian

 

[Fazza]

hello, Brian

I was totally wrong, sorry. The different sizes would be a real problem & I didn't read that carefully in your original post.

Next idea is have some VBA loop through - once for each page to be printed - and print the document one page at a time, setting the page number for each loop/page.

I think that wouldn't be too difficult: I'll have a look at coding it when I get a chance.

regards

 

[Fazza]

hi, Brian

Below tested OK for me, please modify to suit. It is workbook event code - that is to say, it goes in the workbook's code module.

HTH

Private Sub Workbook_BeforePrint(Cancel As Boolean)

  'rough & dirty code to test the idea, print activesheet assumed
  'please tidy up & modify for use

  Dim blnOdd As Boolean
  Dim i As Long
  Dim lngTotalPages As Long
  Dim wks As Worksheet

  Cancel = True
  
  blnOdd = False
  blnOdd = MsgBox(Prompt:="Odd numbers?", Buttons:=vbYesNo) = vbYes

  Set wks = ActiveSheet

  Application.EnableEvents = False

  lngTotalPages = ExecuteExcel4Macro("Get.Document(50)")

  For i = 1 To lngTotalPages
    wks.PageSetup.RightFooter = "Page " & 2 * i + blnOdd * 1
    wks.PrintOut From:=i, To:=i
  Next i
  
  Set wks = Nothing

  Application.EnableEvents = True

End Sub

 

[Kabeer456]

Fazza-

Thank you. I will let you know if it works. I'm in the field most of the week so my response may be delayed. I'm excited to try this as it's been a headache for quite a bit.

Thanks again; I'll be back with my results!

-Brian

 

[Fazza]

OK, Brian. It worked OK for me, so I know it works

Looking afresh I see a superfluous code line "blnOdd = False"

If it isn't exactly what you want, please modify to suit your requirements

cheers

 

[Kabeer456]

Fazza-

Your code worked just fine. I appreciate the effort. Please see below for my final solution as I had to cater to some specific issues such as different users, different page setups, etc. Your example ultimately got me to where I needed to go, so again, many thanks!

I decided to use a user form with a custom message and custom buttons (this was really to eliminate confusion for the user). I went as far as having the user form appear upon opening the file as well.

=======================================

Private Sub PrintEVENHeaders_Click()
'This will allow the user to print ALL pages using ONLY EVEN #s from the current worksheet

'===========================
Dim TotalPages As Long
Dim i As Integer
Dim p As Integer
Dim wks As Worksheet
'===========================

i = 2 'Starts printing as page # 2
TotalPages = ExecuteExcel4Macro("Get.Document(50)") 'Finds total # of pages in worksheet

Set wks = ActiveSheet

For p = 1 To TotalPages 'Applies a header to each page - PAGE COUNTER
wks.PageSetup.RightHeader = "&""Arial""&b&13 Page " & i
wks.PrintOut From:=p, To:=p
i = i + 2 'Skips odd pages
Next p

End Sub
Private Sub PrintODDHeaders_Click()
'This will allow the user to print ALL pages using ONLY ODD #s from the current worksheet

'===========================
Dim TotalPages As Long
Dim i As Integer
Dim p As Integer
Dim wks As Worksheet
'===========================

i = 1 'Stars printing as page # 1
TotalPages = ExecuteExcel4Macro("Get.Document(50)") 'Finds total # of pages in worksheet

Set wks = ActiveSheet

For p = 1 To TotalPages 'Applies a header to each page - PAGE COUNTER
wks.PageSetup.RightHeader = "&""Arial""&b&9 Page " & i
wks.PrintOut From:=p, To:=p
i = i + 2 'Skips even pages
Next p

End Sub

=======================================

I have also assigned to auto open module macro to a custom button so the user can simply access my user form after switching to a different worksheet. So far, it works perfectly for our setup.

Sorry it took me so long to respond. Between other projects, 2 jobs, family issues and tweaking the actual code, I don't have much spare time these days.

I hope I return the favor one day to this message board. Thanks for the wealth of info!

Cheers,
Brian

 

[Fazza]

well done, Brian