| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing-don't need half this code but which half?:-)
Hi,
I've recorded a macro for printing pre-selected ranges. The spreadsheet is going out to lots of users who will all have different ways of setting the print range - some more successful than others. So I thought I would put a button in so that everyone would in theory get the same printout. I'm sure there's lots in here that isn't needed just don't know what. This is a just part of the whole code (prints 11 sheets with varying areas- there are 15 sheets in book). Sheets("Data1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Data2").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Data2" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed End With End Sub I also had a thought that someone here would be able to tell me how to cut down on some of the code I'm sure I need to do With Selection and Endwith but not sure where and what so that properties common to all printing is only coded once. I'm at the record macro and muddle about stage but learning all the time so it would be great is someone could work out what I mean and then help. Many thanks -- TD |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing-don't need half this code but which half?:-)
i don't know exactly what parts of the page set-up code you don't
need, because i can't tell all of what you've changed. but one thing i would do, if i were you, to save space, is to put that page set-up code in a separate macro sub & then just refer to it, instead of spelling it out every time: =========================== sub set_up_page() With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With end sub sub TD() Sheets("Data1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15" Call set_up_page ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Data2").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21" Call set_up_page 'etc, etc, etc end sub ====================== of course, this will only work if your set-up sub is the same each time, which it looks like to me. hope this helps :) susan On Jun 16, 11:46*am, TD wrote: Hi, I've recorded a macro for printing pre-selected ranges. The spreadsheet is going out to lots of users who will all have different ways of setting the print range - some more successful than others. So I thought I would put a button in so that everyone would in theory get the same printout. I'm sure there's lots in here that isn't needed just don't know what. This is a just part of the whole code (prints 11 sheets with varying areas- there are 15 sheets in book). * * With ActiveSheet.PageSetup * * * * .LeftHeader = "" * * * * .CenterHeader = "Data2" * * * * .RightHeader = "" * * * * .LeftFooter = "" * * * * .CenterFooter = "" * * * * .RightFooter = "" * * * * .LeftMargin = Application.InchesToPoints(0.75) * * * * .RightMargin = Application.InchesToPoints(0.75) * * * * .TopMargin = Application.InchesToPoints(1) * * * * .BottomMargin = Application.InchesToPoints(1) * * * * .HeaderMargin = Application.InchesToPoints(0.5) * * * * .FooterMargin = Application.InchesToPoints(0.5) * * * * .PrintHeadings = False * * * * .PrintGridlines = False * * * * .PrintComments = xlPrintNoComments * * * * .CenterHorizontally = False * * * * .CenterVertically = False * * * * .Orientation = xlLandscape * * * * .Draft = False * * * * .PaperSize = xlPaperA4 * * * * .FirstPageNumber = xlAutomatic * * * * .Order = xlDownThenOver * * * * .BlackAndWhite = False * * * * .Zoom = False * * * * .FitToPagesWide = 1 * * * * .FitToPagesTall = 2 * * * * .PrintErrors = xlPrintErrorsDisplayed * * End With * *End Sub * I also had a thought that someone here would be able to tell me how to cut down on some of the code I'm sure I need to do With Selection and Endwith but not sure where and what so that properties common to all printing is only coded once. I'm at the record macro and muddle about stage but learning all the time so it would be great is someone could work out what I mean and then help. Many thanks -- TD |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing-don't need half this code but which half?:-)
If you want to ensure that the printouts are absolutely identical then you
need all of the settings. While it is unlikely that anyone is going to play with 90% of those settings the only way to ensure 100% it to define all of the settings. If however all you want to define is the print area then there is not much to that... With Sheets("Data1") .PageSetup.PrintArea = "$A$1:$F$21" .PrintPreview .PrintOut end with With Sheets("Data2") .PageSetup.PrintArea = "$A$1:$F$15" .PrintPreview .PrintOut end with Note that for all of the other settings whatever the user set them to last is the value that will be used. So if they changed margins or headers or such then that is what they will get. -- HTH... Jim Thomlinson "TD" wrote: Hi, I've recorded a macro for printing pre-selected ranges. The spreadsheet is going out to lots of users who will all have different ways of setting the print range - some more successful than others. So I thought I would put a button in so that everyone would in theory get the same printout. I'm sure there's lots in here that isn't needed just don't know what. This is a just part of the whole code (prints 11 sheets with varying areas- there are 15 sheets in book). Sheets("Data1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Data2").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Data2" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed End With End Sub I also had a thought that someone here would be able to tell me how to cut down on some of the code I'm sure I need to do With Selection and Endwith but not sure where and what so that properties common to all printing is only coded once. I'm at the record macro and muddle about stage but learning all the time so it would be great is someone could work out what I mean and then help. Many thanks -- TD |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing-don't need half this code but which half?:-)
Sorry you can comment out the printpreview. I added that in for debugging. I
try not to kill too many trees when I am trying to get the printing to work out... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: If you want to ensure that the printouts are absolutely identical then you need all of the settings. While it is unlikely that anyone is going to play with 90% of those settings the only way to ensure 100% it to define all of the settings. If however all you want to define is the print area then there is not much to that... With Sheets("Data1") .PageSetup.PrintArea = "$A$1:$F$21" .PrintPreview .PrintOut end with With Sheets("Data2") .PageSetup.PrintArea = "$A$1:$F$15" .PrintPreview .PrintOut end with Note that for all of the other settings whatever the user set them to last is the value that will be used. So if they changed margins or headers or such then that is what they will get. -- HTH... Jim Thomlinson "TD" wrote: Hi, I've recorded a macro for printing pre-selected ranges. The spreadsheet is going out to lots of users who will all have different ways of setting the print range - some more successful than others. So I thought I would put a button in so that everyone would in theory get the same printout. I'm sure there's lots in here that isn't needed just don't know what. This is a just part of the whole code (prints 11 sheets with varying areas- there are 15 sheets in book). Sheets("Data1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Data2").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Data2" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed End With End Sub I also had a thought that someone here would be able to tell me how to cut down on some of the code I'm sure I need to do With Selection and Endwith but not sure where and what so that properties common to all printing is only coded once. I'm at the record macro and muddle about stage but learning all the time so it would be great is someone could work out what I mean and then help. Many thanks -- TD |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing-don't need half this code but which half?:-)
Thank you Susan,
Will look at this later - onto something else at the mo' but will try this asap. Thanks again -- TD "Susan" wrote: i don't know exactly what parts of the page set-up code you don't need, because i can't tell all of what you've changed. but one thing i would do, if i were you, to save space, is to put that page set-up code in a separate macro sub & then just refer to it, instead of spelling it out every time: =========================== sub set_up_page() With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With end sub sub TD() Sheets("Data1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15" Call set_up_page ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Data2").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21" Call set_up_page 'etc, etc, etc end sub ====================== of course, this will only work if your set-up sub is the same each time, which it looks like to me. hope this helps :) susan On Jun 16, 11:46 am, TD wrote: Hi, I've recorded a macro for printing pre-selected ranges. The spreadsheet is going out to lots of users who will all have different ways of setting the print range - some more successful than others. So I thought I would put a button in so that everyone would in theory get the same printout. I'm sure there's lots in here that isn't needed just don't know what. This is a just part of the whole code (prints 11 sheets with varying areas- there are 15 sheets in book). With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Data2" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed End With End Sub I also had a thought that someone here would be able to tell me how to cut down on some of the code I'm sure I need to do With Selection and Endwith but not sure where and what so that properties common to all printing is only coded once. I'm at the record macro and muddle about stage but learning all the time so it would be great is someone could work out what I mean and then help. Many thanks -- TD |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Printing-don't need half this code but which half?:-)
Thanks Jim,
Busy with another bit of work but will try asap. I'm with you on the trees bit :-) Thank you again -- TD "Jim Thomlinson" wrote: Sorry you can comment out the printpreview. I added that in for debugging. I try not to kill too many trees when I am trying to get the printing to work out... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: If you want to ensure that the printouts are absolutely identical then you need all of the settings. While it is unlikely that anyone is going to play with 90% of those settings the only way to ensure 100% it to define all of the settings. If however all you want to define is the print area then there is not much to that... With Sheets("Data1") .PageSetup.PrintArea = "$A$1:$F$21" .PrintPreview .PrintOut end with With Sheets("Data2") .PageSetup.PrintArea = "$A$1:$F$15" .PrintPreview .PrintOut end with Note that for all of the other settings whatever the user set them to last is the value that will be used. So if they changed margins or headers or such then that is what they will get. -- HTH... Jim Thomlinson "TD" wrote: Hi, I've recorded a macro for printing pre-selected ranges. The spreadsheet is going out to lots of users who will all have different ways of setting the print range - some more successful than others. So I thought I would put a button in so that everyone would in theory get the same printout. I'm sure there's lots in here that isn't needed just don't know what. This is a just part of the whole code (prints 11 sheets with varying areas- there are 15 sheets in book). Sheets("Data1").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$15" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.748031496062992) .RightMargin = Application.InchesToPoints(0.748031496062992) .TopMargin = Application.InchesToPoints(0.984251968503937) .BottomMargin = Application.InchesToPoints(0.984251968503937) .HeaderMargin = Application.InchesToPoints(0.511811023622047) .FooterMargin = Application.InchesToPoints(0.511811023622047) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 1 .PrintErrors = xlPrintErrorsDisplayed End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Sheets("Data2").Select With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$1:$F$21" With ActiveSheet.PageSetup .LeftHeader = "" .CenterHeader = "Data2" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlLandscape .Draft = False .PaperSize = xlPaperA4 .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = 2 .PrintErrors = xlPrintErrorsDisplayed End With End Sub I also had a thought that someone here would be able to tell me how to cut down on some of the code I'm sure I need to do With Selection and Endwith but not sure where and what so that properties common to all printing is only coded once. I'm at the record macro and muddle about stage but learning all the time so it would be great is someone could work out what I mean and then help. Many thanks -- TD |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| First Half / Second Half Month Budget | Excel Worksheet Functions | |||
| Compare 1st Half & 2nd Half of 2008. | Excel Worksheet Functions | |||
| printing begins half way down the page | New Users to Excel | |||
| print half of rows on left and other half on right | Excel Discussion (Misc queries) | |||
| Printing gridlines half-tone | Excel Discussion (Misc queries) | |||
Linear Mode
