Select Page Break Areas/Range

[hey yer barred]

Hi All

I am using Excel 2007 and was wondering if anyone knows how to through VBA select each page break area range, copy it and paste it onto a new worksheet?

In a nutshell, I have sevreal Excel workbooks and each has at least 2 - 3 page breaks. I need to select one at a time and copy and paste into another worksheet ... is this at all possible?

 

[Comfy]

This will select the Page Break Range if the are no Vertical Page breaks. You can then copy selection to a New Sheet.

Sub Test()
Dim wb As Workbook, ws As Worksheet, i As Long, j As Long

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
j = 1
    If ws.VPageBreaks.Count = 0 Then
        For i = 1 To ws.HPageBreaks.Count
            Rows(j & ":" & ws.HPageBreaks.Item(i).Location.Row - 1).Select
            j = ws.HPageBreaks.Item(i).Location.Row
        Next i
    End If
      
End Sub

 

[hey yer barred]

You absoulute LEGEND! Thank you so much, I tried all day yesterday to get my head around this and just couldnt do it!

You won't beleive how much this is going to help me.

 

[Comfy]

No probs.

I had no idea how to do it either, but recently have found it quite useful to, in this case.

Set the workbook and Worksheet objects and then using the locals window look through the object properties for something remotely similar to that that I'm looking for.