Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
Hello,
I have an Excel worksheet which has three pages of data and the rest o the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to contro the page or print setting so that blank pages will not be printed? Thank you!! Jaso -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
Suggestion: Record a macro while setting the print area, then examine and
adapt the code. You'll get something like this: ActiveSheet.PageSetup.PrintArea = "$B$1:$B$36" To be specific about which workbook and worksheet you are setting the print area for, you might use Workbooks("Book1.xls").Worksheets("Sheet1").PageSe tup.PrintArea = "$B$1:$B$36" where you replace Book1.xls and Sheet1 with your own workbook and sheet names. "$B$1:$B$36" can be replaced with any statement that returns a range address; e.g., Range(Cells(1,1),Cells(2,2)).Address Regards, Bob "wmcray " wrote in message ... Hello, I have an Excel worksheet which has three pages of data and the rest of the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to control the page or print setting so that blank pages will not be printed? Thank you!! Jason --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
Select the areas you want printed
then do File=Printarea =Set PrintArea If this doesn't do what you want, then you will need to rearrange you data/hide rows you don't want printed and then set your printarea -- Regards, Tom Ogilvy "wmcray " wrote in message ... Hello, I have an Excel worksheet which has three pages of data and the rest of the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to control the page or print setting so that blank pages will not be printed? Thank you!! Jason --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
I can think of two reasons for Excel printing extra pages where there is no
data. First, make sure you don't have a "hard-coded" print range. Select File/Page Setup. Click on the Sheet tab. If there is a range specified in the "Print Range" text box, delete it. The second way you can be getting extra blank pages printing is an improper "usedrange". To prove this is happening, select cell A1 and press <Ctrl-End. If the selected cell ends up well beyond where you have data, then the sheet's usedrange is not accurate. This is a common Excel occurance when you add and delete data or have cells formatted that are beyond the range where you have actual data. You can delete the extra rows and columns, and resave the workbook. When you reopen it, try the above exercise again and the last cell should now contain data. If this is an ongoing problem for you, I suggest putting the following macro into you personal workbook. You can just run this to reset the usedrange. This has the advantage on not having to save the workbook and reopening it. I'm not sure where this came from originally, but I got it from an old post from John Greene. Sub ResetUsedRange() ActiveSheet.UsedRange End Sub HTH Phil Webb "wmcray " wrote in message ... Hello, I have an Excel worksheet which has three pages of data and the rest of the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to control the page or print setting so that blank pages will not be printed? Thank you!! Jason --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
Bob:
Sorry, I am looking around for ideas to solve a problem and this thread seems to be related. Is there any way to code the following issue? I have different worksheets in an XL 2002 file that I want to print in tandem with a macro. I need to reset all possible page breaks and then set the same print area for all of them. I can select all worksheets change the page setup properties for Page, Margins and Headers/Footers, but I can't get access to the options in the Sheet tab related to area and titles. Similarly I can't get to do anything about the Page Breaks. Can you help me? This is part of my code and I don't know what I am doing wrong. Worksheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8")).Select SelectedSheets.ResetAllPageBreaks SelectedSheets.PageSetup.PrintArea = "$G$13:$BE$242" Range("G93").Select SelectedSheets.HPageBreaks.Add Befo=ActiveCell Range("X13").Select SelectedSheets.VPageBreaks.Add Befo=ActiveCell With SelectedSheets.PageSetup .PrintTitleRows = "$1:$12" .PrintTitleColumns = "$A:$F" End with You would really make my week if you have any suggestions. Thanks Roy "Bob Kilmer" wrote: Suggestion: Record a macro while setting the print area, then examine and adapt the code. You'll get something like this: ActiveSheet.PageSetup.PrintArea = "$B$1:$B$36" To be specific about which workbook and worksheet you are setting the print area for, you might use Workbooks("Book1.xls").Worksheets("Sheet1").PageSe tup.PrintArea = "$B$1:$B$36" where you replace Book1.xls and Sheet1 with your own workbook and sheet names. "$B$1:$B$36" can be replaced with any statement that returns a range address; e.g., Range(Cells(1,1),Cells(2,2)).Address Regards, Bob "wmcray " wrote in message ... Hello, I have an Excel worksheet which has three pages of data and the rest of the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to control the page or print setting so that blank pages will not be printed? Thank you!! Jason --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
Sometimes looping through the worksheets works ok.
Dim wks As Worksheet For Each wks In _ Worksheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8")) With wks .ResetAllPageBreaks .PageSetup.PrintArea = "$G$13:$BE$242" Application.Goto .Range("G93") .HPageBreaks.Add Befo=ActiveCell Application.Goto .Range("X13") .VPageBreaks.Add Befo=ActiveCell With .PageSetup .PrintTitleRows = "$1:$12" .PrintTitleColumns = "$A:$F" End With End With Next wks Roy wrote: Bob: Sorry, I am looking around for ideas to solve a problem and this thread seems to be related. Is there any way to code the following issue? I have different worksheets in an XL 2002 file that I want to print in tandem with a macro. I need to reset all possible page breaks and then set the same print area for all of them. I can select all worksheets change the page setup properties for Page, Margins and Headers/Footers, but I can't get access to the options in the Sheet tab related to area and titles. Similarly I can't get to do anything about the Page Breaks. Can you help me? This is part of my code and I don't know what I am doing wrong. Worksheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8")).Select SelectedSheets.ResetAllPageBreaks SelectedSheets.PageSetup.PrintArea = "$G$13:$BE$242" Range("G93").Select SelectedSheets.HPageBreaks.Add Befo=ActiveCell Range("X13").Select SelectedSheets.VPageBreaks.Add Befo=ActiveCell With SelectedSheets.PageSetup .PrintTitleRows = "$1:$12" .PrintTitleColumns = "$A:$F" End with You would really make my week if you have any suggestions. Thanks Roy "Bob Kilmer" wrote: Suggestion: Record a macro while setting the print area, then examine and adapt the code. You'll get something like this: ActiveSheet.PageSetup.PrintArea = "$B$1:$B$36" To be specific about which workbook and worksheet you are setting the print area for, you might use Workbooks("Book1.xls").Worksheets("Sheet1").PageSe tup.PrintArea = "$B$1:$B$36" where you replace Book1.xls and Sheet1 with your own workbook and sheet names. "$B$1:$B$36" can be replaced with any statement that returns a range address; e.g., Range(Cells(1,1),Cells(2,2)).Address Regards, Bob "wmcray " wrote in message ... Hello, I have an Excel worksheet which has three pages of data and the rest of the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to control the page or print setting so that blank pages will not be printed? Thank you!! Jason --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help. Excel Printing Macro
It worked.
Thanks Dave. Roy "Dave Peterson" wrote: Sometimes looping through the worksheets works ok. Dim wks As Worksheet For Each wks In _ Worksheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8")) With wks .ResetAllPageBreaks .PageSetup.PrintArea = "$G$13:$BE$242" Application.Goto .Range("G93") .HPageBreaks.Add Befo=ActiveCell Application.Goto .Range("X13") .VPageBreaks.Add Befo=ActiveCell With .PageSetup .PrintTitleRows = "$1:$12" .PrintTitleColumns = "$A:$F" End With End With Next wks Roy wrote: Bob: Sorry, I am looking around for ideas to solve a problem and this thread seems to be related. Is there any way to code the following issue? I have different worksheets in an XL 2002 file that I want to print in tandem with a macro. I need to reset all possible page breaks and then set the same print area for all of them. I can select all worksheets change the page setup properties for Page, Margins and Headers/Footers, but I can't get access to the options in the Sheet tab related to area and titles. Similarly I can't get to do anything about the Page Breaks. Can you help me? This is part of my code and I don't know what I am doing wrong. Worksheets(Array("E1", "E2", "E3", "E4", "E5", "E6", "E7", "E8")).Select SelectedSheets.ResetAllPageBreaks SelectedSheets.PageSetup.PrintArea = "$G$13:$BE$242" Range("G93").Select SelectedSheets.HPageBreaks.Add Befo=ActiveCell Range("X13").Select SelectedSheets.VPageBreaks.Add Befo=ActiveCell With SelectedSheets.PageSetup .PrintTitleRows = "$1:$12" .PrintTitleColumns = "$A:$F" End with You would really make my week if you have any suggestions. Thanks Roy "Bob Kilmer" wrote: Suggestion: Record a macro while setting the print area, then examine and adapt the code. You'll get something like this: ActiveSheet.PageSetup.PrintArea = "$B$1:$B$36" To be specific about which workbook and worksheet you are setting the print area for, you might use Workbooks("Book1.xls").Worksheets("Sheet1").PageSe tup.PrintArea = "$B$1:$B$36" where you replace Book1.xls and Sheet1 with your own workbook and sheet names. "$B$1:$B$36" can be replaced with any statement that returns a range address; e.g., Range(Cells(1,1),Cells(2,2)).Address Regards, Bob "wmcray " wrote in message ... Hello, I have an Excel worksheet which has three pages of data and the rest of the pages are blank. Upon printing, a lot of blank pages will be printed. Could anyone please instruct me how to write an Excel macro to control the page or print setting so that blank pages will not be printed? Thank you!! Jason --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Macro for Excel 97 | Excel Discussion (Misc queries) | |||
Printing Problem In Excel Due to Macro? | Excel Discussion (Misc queries) | |||
Printing Problems in Excel due to a Macro in Word?! | Excel Discussion (Misc queries) | |||
Printing numbers in Excel. Macro needed. | Excel Programming | |||
Text printing from VBA Excel macro | Excel Programming |