Print only 1 sheet in a workbook.

[boonarga]

I have a workbook with sheets numbered 1 to 20. On another sheet I
have a print button. I need to have the print button work so that it
prints just 1 of those 20 sheets. The sheet number to be printed will
be in a cell on sheet Data3, Cell:C7.

The print button is on another sheet.

I'm at a loss to write a macro that can do this job.
Can anyone help?

Thanks

 

[Gord Dibben]

Sub PrintCopies()
Dim whichone as Long
whichone = Sheets("Data3").Range("C7").Value
Sheets(whichone).PrintOut
End Sub


Gord Dibben MS Excel MVP

 

[Dave Peterson]

Option Explicit
Sub PrintMe()
dim wks as worksheet
set wks = nothing
on error resume next
set wks = worksheets("Data3").range("C7").value
on error goto 0

if wks is nothing then
msgbox "Not a worksheet name in C7 of Data3"
else
wks.printout preview:=true
end if
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

There could be a bug in both Gord's code and mine.

If there's a number in that cell, then you'll want to make sure that you're
printing the sheet named 3--not the 3rd sheet from the left.

Gord's code would change to this:

Sub PrintCopies()
Dim whichone as Long
whichone = Sheets("Data3").Range("C7").Value
Sheets(cstr(whichone)).PrintOut
End Sub


And I assumed that they were worksheets. They could be chart sheets. My code
should be changed to this:

Option Explicit
Sub PrintMe()
dim wks as Object
set wks = nothing
on error resume next
set wks = sheets(cstr(worksheets("Data3").range("C7").value))
on error goto 0

if wks is nothing then
msgbox "Not a worksheet name in C7 of Data3"
else
wks.printout preview:=true
end if
end sub

This line was also wrong in my original suggestion:

set wks = worksheets("Data3").range("C7").value
It should have been:
set wks = worksheets(worksheets("Data3").range("C7").value)
but the cstr() function should have been used, too:
set wks = worksheets(cstr(worksheets("Data3").range("C7").value))

 

[boonarga]

Thanks muchly to both yourself and Gord. I tried Gords suggestion
first and it does exactly what I needed.
I appreciate the time and effort of you both.

Cheers

 

[boonarga]

Update...
It worked fine until I hid all the sheets named 1 to 20.
Can I get it to work with those sheets hidden?

 

[Dave Peterson]

Save the current state.
make it visible (if it isn't)
print it
set it back to that original state


Sub PrintCopies()
Dim whichone as String
Dim IsVisible as long
whichone = cstr(Sheets("Data3").Range("C7").Value)
isVisible = sheets(whichone).visible
sheets(whichone).visible = xlsheetvisible
Sheets(whichone).PrintOut
sheets(whichone).visible = isvisible
End Sub

One of the differences between the code I posted and the code Gord posted was
what'll happen when you have a typo in C7.

 

[boonarga]

Thanks Dave. That worked well. It briefly flashes up the selected
sheet and then prints it.
Only trouble now is that it won't work if I put protection on the
workbook structure. I guess I can live with that.

With cell C7 there can't be any typo as the data in that cell comes
from a dropbox elsewhere.

Thanks again for your time

cheers

 

[Dave Peterson]

You can stop the flickering and unprotect the workbook with something like:

Option Explicit
Sub PrintCopies()
Dim whichone as String
Dim IsVisible as long

Application.screenupdating = false
thisworkbook.unprotect password:="whatever"

whichone = cstr(Sheets("Data3").Range("C7").Value)
isVisible = sheets(whichone).visible
sheets(whichone).visible = xlsheetvisible
Sheets(whichone).PrintOut
sheets(whichone).visible = isvisible

thisworkbook.protect password:="whatever"
application.screenupdating = true

End Sub

 

[boonarga]

That worked brilliantly!! The workbook is now doing everything I want
it to.
Can't thank you enough for your help. Saved me pulling lots of hair
out.

Cheers,
boonarga