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

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

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