Print only 1 sheet in a workbook.
 

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

Print only 1 sheet in a workbook.
 

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


Gord Dibben MS Excel MVP

On 17 Feb 2007 17:31:54 -0800, wrote:

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

Print only 1 sheet in a workbook.
 

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



wrote:

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

--

Dave Peterson

Print only 1 sheet in a workbook.
 

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").va lue))

Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson

Print only 1 sheet in a workbook.
 

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


On Feb 18, 12:07 pm, Dave Peterson wrote:
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").va lue))





Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Print only 1 sheet in a workbook.
 

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


On Feb 18, 2:15 pm, wrote:
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

On Feb 18, 12:07 pm, Dave Peterson wrote:

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").va lue))

Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

Print only 1 sheet in a workbook.
 

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.

wrote:

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

On Feb 18, 2:15 pm, wrote:
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

On Feb 18, 12:07 pm, Dave Peterson wrote:

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").va lue))

Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Print only 1 sheet in a workbook.
 

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



On Feb 18, 11:35 pm, Dave Peterson wrote:
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.



wrote:

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

On Feb 18, 2:15 pm, wrote:
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

On Feb 18, 12:07 pm, Dave Peterson wrote:

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").va lue))

Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

Print only 1 sheet in a workbook.
 

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

wrote:

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

On Feb 18, 11:35 pm, Dave Peterson wrote:
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.



wrote:

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

On Feb 18, 2:15 pm, wrote:
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

On Feb 18, 12:07 pm, Dave Peterson wrote:

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").va lue))

Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

--

Dave Peterson

Print only 1 sheet in a workbook.
 

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




On Feb 19, 1:53 am, Dave Peterson wrote:
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



wrote:

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

On Feb 18, 11:35 pm, Dave Peterson wrote:
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.

wrote:

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

On Feb 18, 2:15 pm, wrote:
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

On Feb 18, 12:07 pm, Dave Peterson wrote:

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").va lue))

Dave Peterson wrote:

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

wrote:

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

--

Dave Peterson

--

Dave Peterson- Hide quoted text -

- Show quoted text -

--

Dave Peterson

--

Dave Peterson