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.


---
Message posted from http://www.ExcelForum.com/

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 *

"courtesio99" wrote in message
...
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.


---
Message posted from http://www.ExcelForum.com/

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"courtesio99" wrote in message ...
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.


---
Message posted from http://www.ExcelForum.com/

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)
www.rondebruin.nl



"Ron de Bruin" wrote in message ...
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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"courtesio99" wrote in message ...
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.


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

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


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"courtesio99" wrote in message ...
Can u please kindly explain the code? I am totally new to this...
Thanks for your help anyway


---
Message posted from http://www.ExcelForum.com/

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?


---
Message posted from http://www.ExcelForum.com/

Hi

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

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



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"courtesio99" wrote in message ...
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?


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com

Do Until Sheet1.Shapes.Count = 0: Sheet1.Shapes(1).Delete: Loop


"courtesio99" wrote in message
...
Geez.. tat works!Thanks!

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

How can I copy all the worksheets over to the new workbook without
copying the buttons?


---
Message posted from http://www.ExcelForum.com/

Rob van Gelder wrote:
[b]Do Until Sheet1.Shapes.Count = 0: Sheet1.Shapes(1).Delete: Loop


What does it mean?


---
Message posted from http://www.ExcelForum.com/

The line of code will remove all the shapes (buttons etc..) from Sheet1

"courtesio99" wrote in message
...
Rob van Gelder wrote:
[b]Do Until Sheet1.Shapes.Count = 0: Sheet1.Shapes(1).Delete: Loop


What does it mean?


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

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.


"courtesio99" wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

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

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)
www.rondebruin.nl



"courtesio99" wrote in message ...
Geez.. tat works!Thanks!

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

How can I copy all the worksheets over to the new workbook without
copying the buttons?


---
Message posted from http://www.ExcelForum.com/