code for printing

I am using excel 2000

From this and other forums I have found and adapted 2 bits of code as follows

The code below is used to print the sheets in a workbook in reverse order eg sheet 78, sheet 77, sheet 76 etc.....

Please Login or Register  to view this content.

The code below is used to print the pages, where there are multiple sheets, of a worksheet in reverse order eg page 2 then page 1

Please Login or Register  to view this content.

My question is can they be combined so that I can print as follows:

sheet 78 page 2, sheet 78 page 1, sheet 77 page 2 sheet 77 page 1 etc......

Maybe like this (untested):

Please Login or Register  to view this content.

Originally Posted by shg

Maybe like this (untested):

Please Login or Register  to view this content.

Thanks shg that worked fine

Sorry to be a pain but I have another bit of code that prints multiple worksheets, where the worksheets print as 2 pages is it possible to alter this code so that page 2 prints first and page 1 second

The code is as follows

Please Login or Register  to view this content.

The worksheets are listed in column B starting at B17, the sheets are listed in the order they need to be printed, but with this code they print page 1 first then page 2 which I would like to reverse

Thx in advance if you can help

Since sheet names must be unique, I don't see how it could ever print more than one sheet, perhaps several times.

Please Login or Register  to view this content.

Hi shg

I've tried this but it does not work, the input box appears and if I enter any of the given initials except CM nothing happens

If I enter CM, I get the following message

Run-time error '9': Subscript out of range

When I click debug the line

With Worksheets(sSht)

is highlighted

There has to be a sheet by that name in the workbook, right?

Please don't quote whole posts, Paul, it just clutters the forum.

Hi shg

It now works with all initials, I had some rows hidden

The info for printing is on the worksheet named "Printing" the AM initials are in column A and the Store Names in Column B starting at row 17

When you say

There has to be a sheet by that name in the workbook

which name are you refering to, the code goes past the worksheet name "Printing" and hangs up at

With Worksheets(sSht)

Thx for your patience, my Visual Basic is very limited, I just search on here and try and adapt where possible or ask as I am now

sSht is a string variable, and Worksheets(sSht) refers to the sheet that has that name. If there isn't one, the code errors.

Sorry I don't understand your last post

My Workbook has the following worksheets

Cover
Printing
Store1 (Town1)
Store2 (Town2)
etc, to
Store78 (Town78)

The worksheet named "Printing" has the AM initials in column A and the Store Names in Column B starting at row 17

Sorry, you're correct; the code goes down col B, but looks for sheet names in col A.

I've switched the Store Names to column A and the AM names to column B but it still does not work

I don't know what's not working, Paul. Either explain further, or (preferably) post the workbook.

Hi shg

Example file attached

Contains 3 macros

Macro 1 -

Sub CPSECompletedQuestionnairesPrintAreaManagersIndividualStores()

This on its own will make the stores for CM if selected print out Store5 then 4then 2, however each worksheet prints as 2 pages and page 1 prints first then page 2

I want the print order to be Store5 Page 2, Store5 Page 1, Store4 Page2, Store 4 Page 1, Store2 Page 2, Store2 Page 1

Macro 2 -

Sub PrintBackwards()

This will print the individual pages of the worksheet in reverse order

Macro3 -

Sub V2CPSECompletedQuestionnairesPrintAreaManagersIndividualStores()

This is the code you gave me which hopefully would have achieved the print order above ie Store5 Page 2, Store5 Page 1, Store4 Page2, Store 4 Page 1, Store2 Page 2, Store2 Page 1

Example file attached, hope this is clear

Try this:

Please Login or Register  to view this content.

Thanks again shg for your persistance and patience

The original document prints out at about 160 pages, this will save collating it manually

You're welcome ... but couldn't you just do this in printer settings -- set page order back to front?