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.
--
Mr Speedy

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

--
Gary''s Student - gsnu200825


"speedy" wrote:

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.
--
Mr Speedy

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.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu200825


"speedy" wrote:

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.
--
Mr Speedy

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

On Sun, 11 Jan 2009 06:37:01 -0800, speedy
wrote:

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.

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






"speedy" wrote:

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.
--
Mr Speedy