Macro that uses range names

[speedy]

I am looking to create a simple macro. I have created some range names,
example is page1 all the way to page10. I want to copy these ranges to
another worksheet. The problem is my macro is only good if I know how many
range pages that I will actually need. I want to know how I can create a
loop that will work for 1 page or 100 pages.

 

[Gary''s Student]

Here is a way to loop over names and to also display the count of names:

Sub NameIt()
Dim n As Name
For Each n In ActiveWorkbook.Names
MsgBox (n.Name & Range(n.Name).Address)
Next
MsgBox (ActiveWorkbook.Names.Count)
End Sub

 

[JLGWhiz]

By pages, do you mean printed pages or are you referring to worksheets.
There can be many pages on a single worksheet. The worksheets are contained
in a workbook.

For printed pages on Sheets(1).

Sub ei()
Sheets(1).PrintPreview
x = Sheets(1).HPageBreaks.Count + 1
MsgBox x
End Sub

For the number of sheets in a workbook

Sub dk()
y = ActiveWorkbook.Sheets.Count
MsgBox y
End Sub

 

[Rick Rothstein]

Besides evoking the Range object, you can also get the address directly from
the Name object itself...

MsgBox n.Name & " " & n.RefersToRange.Address

Also, the parentheses are not required when calling the MsgBox function like
it was a subroutine.

 

[Fred Holmes]

Do you mean that the macro crashes if you specify 100 pages but there
are only 15 pages? Use error trapping.

On Error GoTo Label01
[here use the x100 loop that works]
Label01:
End Sub

IIRC the "Label01:" has to start in column 1. You cannot indent it.

Fred Holmes