Multi Area Header (2000)

Topic

New Reply

Hi Guys:

In VBA, is there a way of printing say rows 1 – 4 of the sheet and then rows 8 – 12 on each page? On the first page of the printout they’re already there, of course, but on subsequent sheets rows 5 – 7 should not appear, so I can’t use ‘Columns to repeat at top’.

To complicate matters, the range (number of rows) to be printed will vary, and is determined in code. Also, some rows may be multi-height (word-wrapped) so there is not a set number of rows on each page.

At present, the code locates the auto page breaks at print time and prints the appropriate ranges at the top of each page, but this runs very slowly.

Any ideas for a different approach welcome.

Reply | Quote

Replies

Couldn’t you simplify the design of the workbook?
Or perhaps create worksheets with copies of the areas that you want to print…

Reply | Quote

Hi Hans:

Unfortunately, the w/b design has to match design considerations elsewhere (CAD, I think – it’s not my w/.

I’ll play with the copy to new w/s idea.

Thanks,

Reply | Quote

If I understand your example correctly you want the following printout:

• Page 1:- Rows 1 through 12
• All subsequent pages-: Rows 1 through 4, and 8 through 12
  [/list]Please confirm or restate the requirement.

Reply | Quote

Try the following:

Sub SelectivelyPrint()

Const FirstHidden = 5
Const LastHidden = 7
Const LastPrintCol = "AA"
Const LastPrintRow = 12
Const MaxPagesPrint = 1000

    ActiveSheet.PageSetup.PrintArea = _
        "$A$1:$" & LastPrintCol & "$" & LastPrintRow    
    ActiveWindow.SelectedSheets.PrintOut _
        From:=1, To:=1, Copies:=1, Collate _
        :=True
    Rows(FirstHidden & ":" & LastHidden) _
        .EntireRow.Hidden = True
    ActiveWindow.SelectedSheets.PrintOut _
        From:=2, To:=MaxPagesPrint, Copies:=1, _
        Collate:=True
    Rows(FirstHidden & ":" & LastHidden) _
        .EntireRow.Hidden = False
End Sub

H.T.H.

Reply | Quote