Printing Help Needed

sinned141

Board Regular
Joined
May 4, 2009
Messages
115
Hi all

I have recorded the following code on my master copy sheet however I would like to be able to print off all sheets which start with "BOQ Block" using the same layout, is there a way I can do this using a macro?
there are a different number of sheets each time so I cant just record it as I have done with this.

Code:
Sub PrintMe()
'
' PrintClientCopy Macro
' This Prints The First BOQ sheet
'
    Application.ScreenUpdating = False
    Columns("E:J").Select
    Selection.EntireColumn.Hidden = True
    Range("Table1[[#Headers],[ITEM]]").Select
    ActiveSheet.ListObjects("Table1").ShowTotals = True
    Range("Table1[[#Totals],[MATERIAL]:[TOTAL]]").Select
    ActiveSheet.ListObjects("Table1").ListColumns("TOTAL").TotalsCalculation = _
        xlTotalsCalculationSum
    ActiveSheet.ListObjects("Table1").ListColumns("MATERIAL").TotalsCalculation = _
        xlTotalsCalculationSum
    ActiveSheet.ListObjects("Table1").ListColumns("lABOUR").TotalsCalculation = _
        xlTotalsCalculationSum
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$7"
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .RightHeader = "&G"
        .LeftFooter = _
        "&8&K00-049Anything" & Chr(10)
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&K00-049&F"
        .PrintComments = xlPrintSheetEnd
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    ActiveWindow.View = xlPageLayoutView
    ActiveWindow.SmallScroll Down:=0
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$7"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .RightHeader = "&G"
        .LeftFooter = _
        "&8&K00-049Anything" & Chr(10)
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&K00-049&F"
        .Orientation = xlPortrait
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveWindow.View = xlNormalView
    Columns("D:K").Select
    Range("K1").Activate
    Selection.EntireColumn.Hidden = False
    Range("A1").Select
    ActiveSheet.ListObjects("Table1").ShowTotals = False
    Application.ScreenUpdating = True
End Sub

Many Thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
sinned141,

Perhaps something like this as your primary print code that will call your existing code.

Code:
Sub Print_BOQ_Block()
For Each Sht In ThisWorkbook.Sheets
If Left((Trim(Sht.Name)), 9) = "BOQ Block" Then
Sht.Select
Call PrintMe
End If
Next Sht
End Sub

Not tested but hope it helps.
 
Upvote 0
Thank you Snakehips

I think that bit works however I get a runtime error because in my code it refers to table 1 and on each sheet the table number changes, anyone got any ideas how i can get around that?

Code:
 Range("Table1[[#Headers],[ITEM]]").Select
    ActiveSheet.ListObjects("Table1").ShowTotals = True

thats where it stops

Many Thanks
 
Upvote 0
sinned141,

Do you just have one table in each of the sheets that you will be printing?
 
Upvote 0
Hi Snakehips

Yes each of the sheets has one table, they are all populated from the same template sheet
 
Upvote 0
sinned141,

Again, this is not tested but try the revised code below on a copy of your workbook.

Code:
Sub PrintMe()
'
' PrintClientCopy Macro
' This Prints The First BOQ sheet
'
    Application.ScreenUpdating = False
    Columns("E:J").EntireColumn.Hidden = True
    
    ActiveSheet.ListObjects(1).ShowTotals = True
    
    ActiveSheet.ListObjects(1).ListColumns("TOTAL").TotalsCalculation = _
        xlTotalsCalculationSum
    ActiveSheet.ListObjects(1).ListColumns("MATERIAL").TotalsCalculation = _
        xlTotalsCalculationSum
    ActiveSheet.ListObjects(1).ListColumns("lABOUR").TotalsCalculation = _
        xlTotalsCalculationSum
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$7"
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .RightHeader = "&G"
        .LeftFooter = _
        "&8&K00-049Anything" & Chr(10)
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&K00-049&F"
        .PrintComments = xlPrintSheetEnd
        .Orientation = xlPortrait
        .FitToPagesWide = 1
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    ActiveWindow.View = xlPageLayoutView
    ActiveWindow.SmallScroll Down:=0
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$7"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .RightHeader = "&G"
        .LeftFooter = _
        "&8&K00-049Anything" & Chr(10)
        .CenterFooter = "Page &P of &N"
        .RightFooter = "&K00-049&F"
        .Orientation = xlPortrait
        .PaperSize = xlPaperA4
        .FitToPagesWide = 1
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    ActiveWindow.View = xlNormalView
    Columns("D:K").Select
    Range("K1").Activate
    Selection.EntireColumn.Hidden = False
    Range("A1").Select
    ActiveSheet.ListObjects(1).ShowTotals = False
    Application.ScreenUpdating = True


Let me know how it goes.
 
Upvote 0
Solution
Snakehips

you are a star, that's brilliant
thank you so much.

I dont suppose you have any idea how I could also create create a totals page which has the totals from each sheet, I am not sure if it would be better to have the total page set up beforehand or after?
 
Upvote 0
sinned141,

Perhaps something like this....

Determine the name of the table in each sheet then set up a totals page.


Excel 2007
ABCDEFG
1Col1Col2Col3Col4Col5Col6
2Table10007900
3Table20689999
4Table37151014147
5Total783191022316
Totals
Cell Formulas
RangeFormula
B2=SUM(Table1[Column1])
B3=SUM(Table2[Column1])
B4=SUM(Table3[Column1])
B5=SUM(B2:B4)
C2=SUM(Table1[Column2])
C3=SUM(Table2[Column2])
C4=SUM(Table3[Column2])
C5=SUM(C2:C4)


Hope that helps.
 
Upvote 0
Thats great thanks
I tried that to begin with but because the amount of sheets I have changes it means I have to alter it each time

Thank you for all yolur help
 
Upvote 0
sinned141,


Do all the tables have same number of columns and if so, how many.
Approximately, how many sheets are we talking in the print routine?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top