Using worksheet name in a macro

Topic

New Reply

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

Reply | Quote

Replies

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”)

Reply | Quote

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!
Computer Specs

Reply | Quote

Thank you for your repsonses. Exactly what I was looking for.
Bernard

Reply | Quote