Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
I think the best thing you can do is
copy the worksheet remove the fill 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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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 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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing worksheet
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Worksheet | Excel Worksheet Functions | |||
Worksheet printing | New Users to Excel | |||
Printing worksheet | New Users to Excel | |||
Worksheet printing | Excel Worksheet Functions | |||
Printing worksheet | New Users to Excel |