Pasting over Horizontal Page Break - Excel VBA 1

[ThomasLafferty]

I am using a macro to repetitively paste a preformatted block of cells. This block will vary in size according to user input. Unfortunately, this means that occasionally a block of formatted cells will fall across a horizontal page break. If this occurs, I would like to paste the whole block at the top of the next available page.

Visually, here's what I mean:

 BEFORE
 ____________
 |          |
 |  CELLS   |
[!]_|__________|__
 |          |[/!]
 |          |
 |__________|

 AFTER
[!]____________[/!]
 |          |
 |  CELLS   |
 |          |
 |          |
 |          |
 |__________|

Here is the relevant part of the code I am using:

'Find and copy range
    Sheets("Layout").Select
    Set Target = Cells(intRowStart, 1)
    Range(Target, Cells(Target.Row + intRowCount, intColumnCount)).Copy
  
'Determine number of copies to make (times to paste)
    If Commodities = Destinations Then
        intTablesToPaste = Commodities
    Else
        If Commodities < Destinations Then
            intTablesToPaste = Commodities
        Else
            intTablesToPaste = Destinations
        End If
    End If
    
'Build tables on rate letter
    Sheets("Rates").Select

'Reinitialize variable
Set Target = Cells(19, 1)
    For BuildTable = 1 To intTablesToPaste
        If BuildTable = 1 Then
            Target.Select
            ActiveSheet.Paste
        Else
        [!]'Need to test for HPageBreak here[/!]
            Target.Offset(intRowCount + 2, 0).Select
            Set Target = Selection
            ActiveSheet.Paste
        End If
    Next BuildTable

I have manually inserted page breaks before row 59, 117, 175 etc (every 58 rows thereafter ending at row 696).

I know how to test the location of a given page break, but I'm not sure how to incorporate this:

HPB = Sheets(2).HPageBreak(1).Location.Row

What I will need to check is if data appears in row 58 and 59 or 116 and 117 etc.

Any thoughts?

Tom

Born once die twice; born twice die once.

 

[CautionMP]

ThomasLafferty,
This is a little messy and I'm struggling to clean it up because we code in two different styles. I went ahead and used the [tt]HPageBreaks[/tt] collection instead of hard coding for the page breaks which I think is a little more durable with end users.

Dim wks As Worksheet
Dim hpb As HPageBreak
Set wks = ActiveSheet
Application.ScreenUpdating = False
wks.DisplayPageBreaks = True
'...
        [red][b]'Need to test for HPageBreak here[/b][/red]
            Target.Offset(intRowCount + 2, 0).Select
            Set Target = Selection
            [b]For Each hpb In wks.HPageBreaks
              If Target.Row <= hpb.Location.Row And _
                 hpb.Location.Row <= Target.Row + intRowCount Then
                Target.Offset(hpb.Location.Row - Target.Row, 0).Select
                Exit For
              End If
            Next hpb[/b]
            ActiveSheet.Paste
'...
wks.DisplayPageBreaks = False
Application.ScreenUpdating = True

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[ThomasLafferty]

Caution -
Thank you for your help! Have a star!
I did need to make one slight change (reset Target = Selection) as noted below, but it worked!

            For Each hpb In wks.HPageBreaks
              If Target.Row <= hpb.Location.Row And _
                 hpb.Location.Row <= Target.Row + intRowCount Then
                Target.Offset(hpb.Location.Row - Target.Row, 0).Select
[!]                Set Target = Selection[/!]
                Exit For
              End If
            Next hpb

Thanks again!

Tom


Born once die twice; born twice die once.