Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I work for a printing company. When we print a job, we need to “layout” the print on the page to insure that there is sufficient “white space” around the sides of the paper and down the middle of the page so that the grippers on the press do not mark the ink on the paper. Depending on which press the job is printed on and how the end product is bound, there are over 100 different layout options to choose from to ensure that the “white space” is adequate.

    I have a spreadsheet that shows each of the 100 different layouts on a separate worksheet. On the main page, I ask for different input options such as what press will the job be printed on, how the job be bound, what is the size of the sheet of paper that the job will be on, etc. For each question, a code is generated based on the answer. I then combine all the codes to generate one big code.

    What I want to do is use that code to select which of the 100 layouts to print. So if code is ABCDEF, then go to worksheet ABCDEF and print that page. So I want to create a macro that would take the code that is generated as in input, then select the appropriate worksheet based on the code, and finally print the appropriate worksheet. Is it possible to use the worksheet name in a macro? How do I get the macro to automatically enter the code? Any other suggestions on how best to do this would be appreciated.

    Bernard

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Suppose your code is in cell A1, on a worksheet called "Home" (without the quotes), then this macro selects the sheet with the name shown in that cell:

    Code:
    Sub GotoSheet()
    Worksheets(Worksheets("Home").Range("A1").Value).Select
    End Sub
    You could also use the Hyperlink function in a cell to create a hyperlink to the appropriate worksheet, no VBA code needed:
    =HYPERLINK("#'"&A1&"'!A1","Click here to go to Print layout sheet")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Assuming all your layouts have the same (Excel) page layout you can add the following to the code above to print it.
    Code:
     ActiveWindow.SelectedSheets.PrintOut Copies:=1  'Print It!
    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    New Lounger
    Join Date
    May 2003
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your repsonses. Exactly what I was looking for.
    Bernard

Posting Permissions

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