How to print specific worksheets

[Susan Hayes]

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

 

[Tom Ogilvy]

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

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

 

[Roger Govier]

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)