Printing worksheet

C

courtesio99

I have a worksheet whereby the cells are of different colours.
How can I write a print function to print the worksheet but the cells
should be of "no fill"? The cells should be of the original colour
after the print.
 
A

A.W.J. Ales

I don't think you can do exactly that Coutesio.

But you can write a macro to make a copy (if necessary values only) of your
sheet, hiding the cells you don't want to print, printing THAT sheet and
deleting the sheet thereafter.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
R

Ron de Bruin

I think the best thing you can do is

copy the worksheet
remove the fill
Print
Delete the sheet

This example is for Sheet1

Sub test()
Application.ScreenUpdating = False
Worksheets("Sheet1").Copy After:=Worksheets(Sheets.Count)
Worksheets(Sheets.Count).Cells.Interior.ColorIndex = xlNone
Worksheets(Sheets.Count).PrintOut

Application.DisplayAlerts = False
Worksheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End Sub
 
R

Ron de Bruin

Better use this one because if you have a Chart sheet in
your workbook it will not work correct.

Sub test2()
Application.ScreenUpdating = False
Worksheets("Sheet1").Copy After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Cells.Interior.ColorIndex = xlNone
Sheets(Sheets.Count).PrintOut

Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Ron de Bruin said:
I think the best thing you can do is

copy the worksheet
remove the fill
Print
Delete the sheet

This example is for Sheet1

Sub test()
Application.ScreenUpdating = False
Worksheets("Sheet1").Copy After:=Worksheets(Sheets.Count)
Worksheets(Sheets.Count).Cells.Interior.ColorIndex = xlNone
Worksheets(Sheets.Count).PrintOut

Application.DisplayAlerts = False
Worksheets(Sheets.Count).Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End Sub
Click to expand...
 
C

courtesio99

Can u please kindly explain the code? I am totally new to this...
Thanks for your help anyway
 
R

Ron de Bruin

Good morning

Sub test2()
Application.ScreenUpdating = False
'You won't be able to see what the macro is doing now

Worksheets("Sheet1").Copy After:=Sheets(Sheets.Count)
'It will make a copy of "sheet1" and place it after the last
'sheet in your workbook. <After:=Sheets(Sheets.Count)>

Sheets(Sheets.Count).Cells.Interior.ColorIndex = xlNone
' we use the index of the sheet instead of the Sheet name
' <Sheets(Sheets.Count)> will refer to the last sheet in the workbook
' this is now the copy of "Sheet1"
' this will remove the fillColor of all cells in this sheet
' <.Cells.Interior.ColorIndex = xlNone>

Sheets(Sheets.Count).PrintOut
'print the sheet

' delete the sheet without asking you if it is OK
Application.DisplayAlerts = False
Sheets(Sheets.Count).Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = False
End Sub
 
C

courtesio99

Thanks a lot man!
You are indeed a great help!

by the way, should the 2nd last line be
Application.ScreenUpdating = True?

And how should I modify the code if I have 4 sheets and I want to print
all the 4 sheets?
 
R

Ron de Bruin

Hi
by the way, should the 2nd last line be
Application.ScreenUpdating = True?
Click to expand...

Yes

That happen when you copy the first line<g>

Try this (change the sheet names)

Sub Test3()
Dim Nwb As Workbook
Application.ScreenUpdating = False
Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")).Copy
' copy the sheets in a new workbook

Set Nwb = ActiveWorkbook
Nwb.Sheets.Select
Cells.Select
Selection.Interior.ColorIndex = xlNone

Nwb.PrintOut
Nwb.Close False
Application.ScreenUpdating = True
End Sub
 
C

courtesio99

Geez.. tat works!Thanks!

Now I have another problem.. haha.
I have some buttons in my original workbook, but when I want to prin
the worksheets, the buttons should not be shown.

How can I copy all the worksheets over to the new workbook withou
copying the buttons
 
C

courtesio99

I'm very sorry... but the line of code doesnt seem to work..
Why is it Sheet1 and not Sheets(1)?
And I don't understand wat is Shapes(1).Delete
 
R

Rob van Gelder

Sheet1 is the codename of my first workbook. You could use Worksheets(1) if
you prefer.
Each worksheet has a collection of Shapes. I keep removing the Shape in
position 1 in the collection until there are no more shapes left.
 
R

Ron de Bruin

Hi

In the properties of a control toolbox button you can tell if it print or not
If you use a Forms button then right click on the button and choose Format control(see properties Tab)

Or do you want to do it with VBA
 
Top