Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print specific worksheets
Some background to what I'm trying to do:
In Worksheet1 I have designed an order entry screen where the end user enters several variables such as country, quantity, price, etc. When all the required information is entered formulas calculate the remaining info such as commissions, levies etc. based on the respective market chosen by the end user. This info found in specific cells in Worksheet1 is linked to 21 other separate Worksheets that are representative of each different market/country. I have introduced a Command Button in Worksheet1 called "Process" which will enable the correct Worksheet to be printed based on the country selected. Eg. If Worksheet1 chose the country as Germany found in Worksheet5 then when the command button was pushed it would print this specific Worksheet5. This is what I am trying to do. I have designed it this way (21 independent worksheets) because each invoice/worksheet for each respective country has specific terms and conditions required for the customer viewing. In Worksheet1 I have written: Private Sub CommandButton1_Click() Dim country As String country = Cells(6, "B").Value Select Case country Case "United Kingdom": Case "Hong Kong": Case etc. … End Select I have not been successful in writing the code to make the required worksheet print. I would also like to hide these Worksheets2 through 22 to keep them from view of the end user, they would only be able to view the hard copy form when printed. Thank you, Susan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print specific worksheets
Application.SceenUpdating = False
Select Case country Case "United Kingdom" With Worksheets("Sheet2") .Visible = xlSheetVisible .Printout .Visible = xlSheetHidden End With Case "Hong Kong": With Worksheets("Sheet3") .Visible = xlSheetVisible .Printout .Visible = xlSheetHidden End With Case etc. Application.ScreenUpdating = True -- Regards, Tom Ogilvy "Susan Hayes" wrote in message ... Some background to what I'm trying to do: In Worksheet1 I have designed an order entry screen where the end user enters several variables such as country, quantity, price, etc. When all the required information is entered formulas calculate the remaining info such as commissions, levies etc. based on the respective market chosen by the end user. This info found in specific cells in Worksheet1 is linked to 21 other separate Worksheets that are representative of each different market/country. I have introduced a Command Button in Worksheet1 called "Process" which will enable the correct Worksheet to be printed based on the country selected. Eg. If Worksheet1 chose the country as Germany found in Worksheet5 then when the command button was pushed it would print this specific Worksheet5. This is what I am trying to do. I have designed it this way (21 independent worksheets) because each invoice/worksheet for each respective country has specific terms and conditions required for the customer viewing. In Worksheet1 I have written: Private Sub CommandButton1_Click() Dim country As String country = Cells(6, "B").Value Select Case country Case "United Kingdom": Case "Hong Kong": Case etc. . End Select I have not been successful in writing the code to make the required worksheet print. I would also like to hide these Worksheets2 through 22 to keep them from view of the end user, they would only be able to view the hard copy form when printed. Thank you, Susan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to print specific worksheets
Hi Sarah
Building on to the excellent answer given to you by Tom, it just struck me, that if you named your sheets as the country, rather than Sheet1 etc. you could cut the code down to something like Dim country As String country = Cells(6, "B").Value Application.ScreenUpdating = False With Worksheets(country) .Visible = xlSheetVisible .PrintOut .Visible = xlSheetHidden End With to save on all the repetition of case statements (The above amends the small typo in Tom's Application.ScreenUpdating) -- Regards Roger Govier "Susan Hayes" wrote in message ... Some background to what I'm trying to do: In Worksheet1 I have designed an order entry screen where the end user enters several variables such as country, quantity, price, etc. When all the required information is entered formulas calculate the remaining info such as commissions, levies etc. based on the respective market chosen by the end user. This info found in specific cells in Worksheet1 is linked to 21 other separate Worksheets that are representative of each different market/country. I have introduced a Command Button in Worksheet1 called "Process" which will enable the correct Worksheet to be printed based on the country selected. Eg. If Worksheet1 chose the country as Germany found in Worksheet5 then when the command button was pushed it would print this specific Worksheet5. This is what I am trying to do. I have designed it this way (21 independent worksheets) because each invoice/worksheet for each respective country has specific terms and conditions required for the customer viewing. In Worksheet1 I have written: Private Sub CommandButton1_Click() Dim country As String country = Cells(6, "B").Value Select Case country Case "United Kingdom": Case "Hong Kong": Case etc. . End Select I have not been successful in writing the code to make the required worksheet print. I would also like to hide these Worksheets2 through 22 to keep them from view of the end user, they would only be able to view the hard copy form when printed. Thank you, Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to print a workbook but worksheets have diff print areas | Excel Discussion (Misc queries) | |||
print button macro & specific tabs to print | Excel Worksheet Functions | |||
How do I print a workbook in but only print selected worksheets? | Excel Discussion (Misc queries) | |||
print specific cells | Excel Discussion (Misc queries) | |||
print a specific area within a worksheet by clicking on print? | Excel Worksheet Functions |