Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing Macro
Hi There,
I have 6 tables on a worksheet - these are ranges: B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137 Now, sometimes I will have notes at the side of the table - I put these into column J, so that any notes for the first tabe would appear in a cell in the range J2:J22 ; any notes for the second table would appear in a cell in the range J25:J45 ; etc. What I want is a macro that will print each of the 6 tables above on a separate sheet (landscape, centred horizontally & vertically, with Row & column Headings), and if the table has notes in column J, these should be displayed on the same sheet. I.e. if the table does not have any notes in column J, then I don't want to see column J appear in the printout, as this reduces unneccessarily the size of the text in the printout. So I need a macro which prints out each table, but before doing so does a search on the relative range in Column J (for that table) and decides whether there are notes in that range and therefore whether to include column J in the printout for that table. Thanks for any help, Gary T. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing Macro
Dim rng as Range, rng1 as rng, rng2 as rng
Dim ar as Range set rng = Range("B2:I22, B25:I45,B48:I68, B71:I91, B94:I114, B117:I137") for each ar in rng.Areas set rng1 = ar.offset(0,8).Resize(,1) if application.CountA(rng1) = 0 then rng2 = ar else rng2 = ar.Resize(,ar.columns.count+1) End if Activesheet.PageSetup.PrintArea = rng2.Address(external:=True) Activesheet.Printout Next You can turn on the macro recorder while you manually make you print settings and get the code you need for that. -- Regards, Tom Ogilvy "Gary T" wrote in message ... Hi There, I have 6 tables on a worksheet - these are ranges: B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137 Now, sometimes I will have notes at the side of the table - I put these into column J, so that any notes for the first tabe would appear in a cell in the range J2:J22 ; any notes for the second table would appear in a cell in the range J25:J45 ; etc. What I want is a macro that will print each of the 6 tables above on a separate sheet (landscape, centred horizontally & vertically, with Row & column Headings), and if the table has notes in column J, these should be displayed on the same sheet. I.e. if the table does not have any notes in column J, then I don't want to see column J appear in the printout, as this reduces unneccessarily the size of the text in the printout. So I need a macro which prints out each table, but before doing so does a search on the relative range in Column J (for that table) and decides whether there are notes in that range and therefore whether to include column J in the printout for that table. Thanks for any help, Gary T. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing Macro
Hi Gary
Try this, it is set up to have two subs, the first one calls the second with the range of each table, the second when called checks the relevant column J cells for valid text (spaces are ignored) and adjust print accordingly. Assumes you are the active worksheet contains the tables, if not then you need to modify to suit. ' first routine to set range for tables first value = row 1 second value = last row Sub PrintTables() Call PrintRange(2, 22) Call PrintRange(25, 45) Call PrintRange(48, 68) Call PrintRange(71, 91) Call PrintRange(94, 114) Call PrintRange(117, 137) End Sub ' routine to print table testing if j startRow to EndRow has text Sub PrintRange(SRow As Long, ERow As Long) Dim ir As Long, iNotes As Boolean iNotes = False For ir = SRow To ERow If Len(Trim(Range("J" & ir).Text)) 0 Then iNotes = True Next ir If iNotes Then MsgBox "Notes" ActiveSheet.PageSetup.PrintArea = "B" & SRow & ":J" & ERow Else ActiveSheet.PageSetup.PrintArea = "B" & SRow & ":I" & ERow End If With ActiveSheet.PageSetup .CenterHorizontally = True .CenterVertically = True .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With ActiveWindow.SelectedSheets.PrintOut Copies:=1 End Sub -- Cheers Nigel "Gary T" wrote in message ... Hi There, I have 6 tables on a worksheet - these are ranges: B2:I22 ; B25:I45 ; B48:I68 ; B71:I91 ; B94:I114 ; B117:I137 Now, sometimes I will have notes at the side of the table - I put these into column J, so that any notes for the first tabe would appear in a cell in the range J2:J22 ; any notes for the second table would appear in a cell in the range J25:J45 ; etc. What I want is a macro that will print each of the 6 tables above on a separate sheet (landscape, centred horizontally & vertically, with Row & column Headings), and if the table has notes in column J, these should be displayed on the same sheet. I.e. if the table does not have any notes in column J, then I don't want to see column J appear in the printout, as this reduces unneccessarily the size of the text in the printout. So I need a macro which prints out each table, but before doing so does a search on the relative range in Column J (for that table) and decides whether there are notes in that range and therefore whether to include column J in the printout for that table. Thanks for any help, Gary T. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing Macro
Nigel,
Thanx, That worked perfectly Regards, Gary. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Macro how do I | Excel Worksheet Functions | |||
Printing Macro | Excel Discussion (Misc queries) | |||
Macro for printing | Excel Worksheet Functions | |||
printing using a macro | Excel Discussion (Misc queries) | |||
Printing in a Macro | Excel Programming |