Printing worksheet

  • Thread starter Thread starter courtesio99
  • Start date Start date
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.
 
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 *
 
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
 
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...
 
Can u please kindly explain the code? I am totally new to this...
Thanks for your help anyway
 
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
 
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?
 
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
 
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
 
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
 
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.
 
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
 
Back
Top