Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Print Macro with seperate headers

    I have a macro to print a worksheet.

    When printing I want "revenue" Cell A2 to print in the header from A3 to A27 and "Costs" A28 to print in he header thereafter

    See Attached file containing my print macro

    Your assistance in resolving this is most appreciated
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Managed to sort out my macro by breaking it down into two sections

    Code:
     Sub Print_Income_Statement()
     Sheets("Income Statement").Select
    
    Dim LastRow As Long
    LastRow = Sheets("Income Statement").Cells(Rows.Count, "A").End(xlUp).Row
    Sheets("Income Statement").Select
     Application.PrintCommunication = False
    With ActiveSheet.PageSetup
    .PrintGridlines = True
    .PrintArea = "A2:E25"
    .PrintTitleRows = "$1:$1"
    .PrintTitleColumns = ""
    .LeftHeader = "&D&T"
    .CenterHeader = Format(Range("A1"), "mmm yyyy")
    
     .Orientation = xlLandscape
    
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    With ActiveSheet.PageSetup
    .PrintGridlines = True
    .PrintArea = "A27:E80"
    .PrintTitleRows = "$26:$26"
    .PrintTitleColumns = ""
    .LeftHeader = "&D&T"
    .CenterHeader = Format(Range("A1"), "mmm yyyy")
    
     .Orientation = xlLandscape
    
    End With
    Application.PrintCommunication = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    
    
    
    End Sub

  3. #3
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Print setup is about the SLOWEST thing you can do in vba.
    Since you didn't post your actual file with the amended code, I will suggest that you use a macro to HIDE the rows you don't want to print and then print and unhide. Simple

  4. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the advice

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •