+ Reply to Thread
Results 1 to 16 of 16

code for printing

  1. #1
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479

    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......
    Last edited by Paul Sheppard; 01-07-2009 at 07:49 PM.
    Paul

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Maybe like this (untested):
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Quote Originally Posted by shg View Post
    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

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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.

  5. #5
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    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
    Last edited by shg; 01-07-2009 at 02:42 PM. Reason: deleted spurious quote

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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.

  7. #7
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    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

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    sSht is a string variable, and Worksheets(sSht) refers to the sheet that has that name. If there isn't one, the code errors.

  9. #9
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    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

  10. #10
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Sorry, you're correct; the code goes down col B, but looks for sheet names in col A.

  11. #11
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    I've switched the Store Names to column A and the AM names to column B but it still does not work

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    I don't know what's not working, Paul. Either explain further, or (preferably) post the workbook.

  13. #13
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    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
    Attached Files Attached Files

  14. #14
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Try this:
    Please Login or Register  to view this content.

  15. #15
    Valued Forum Contributor
    Join Date
    06-30-2005
    Location
    Verwood, Dorset, England
    MS-Off Ver
    Excel 2000
    Posts
    479
    Thanks again shg for your persistance and patience

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

  16. #16
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You're welcome ... but couldn't you just do this in printer settings -- set page order back to front?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1