Try this, Column E is the worksheet name.
Sub SwitchPortSummary()
Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range
Dim n As Name
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Summary")
Set r = ws.Range("A2")
For Each n In wb.Names
r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7)
r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8,
n.Name, "port") - 3)
r.Offset(0, 2).Value = Range(n).Value
r.Offset(0, 3).Value = n.Name
r.Offset(0, 4).Value = Mid(n, 2, InStr(2, n, "!") - 2) ' puts
the worksheet name in col E
Set r = r.Offset(1, 0)
Next n
Set r = ws.Cells
r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Set r = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Chip Pearson wrote:
Try something like
Dim Nam As Name
On Error Resume Next
For Each Nam In ActiveWorkbook.Names
If Not Nam.RefersToRange Is Nothing Then
MsgBox Nam.RefersToRange.Worksheet.Name
End If
Next Nam
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
"Kevin" wrote in message
...
one more thing I need to do. I need a way to get the worksheet name that
a
named cell resides.
I tried this...
Dim Nam
For Each Nam In ActiveWorkbook.Names
MsgBox Nam.Worksheet.Name
Next Nam
"Jason Lepack" wrote:
Kevin,
Is this kind of what you're looking for? If not, you can modify it a
bit. If something bugs you reply back here.
Cheers,
Jason Lepack
Sub SwitchPortSummary()
Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range
Dim n As Name
Set wb = ActiveWorkbook
Set ws = wb.Sheets("Summary")
Set r = ws.Range("A2")
For Each n In wb.Names
r.Value = Mid(n.Name, 7, InStr(8, n.Name, "port") - 7)
r.Offset(0, 1).Value = Right(n.Name, Len(n.Name) - InStr(8,
n.Name, "port") - 3)
r.Offset(0, 2).Value = Range(n).Value
r.Offset(0, 3).Value = n.Name
Set r = r.Offset(1, 0)
Next n
Set r = ws.Cells
r.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1,
MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal
Set r = Nothing
Set ws = Nothing
Set wb = Nothing
End Sub
Jason Lepack wrote:
Sorry for the confusion. When I get home tonight I'll look at your
situation in a little more detail. However, for now, your names are
not specific to your worksheet, they are specific to your workBOOK.
So, go from there!
Sub Macro1()
Dim wb As Workbook
Dim n As Name
Set wb = ActiveWorkbook
For Each n In wb.Names
Debug.Print n.Name
Next n
End Sub
Cheers,
Jason Lepack
Kevin wrote:
Yes, the name of cell a1 would be switch1port1 and the text inside
the cell
would say host1 I will email you a page..
"Jason Lepack" wrote:
Do you mean that you have actually "named" the cell "A1" to
"switch1port1" or is the value of cell "A1" = "switch1port1"?
I think I know what your saying... Could you please give an
example of
what your sheet looks like?
You could email me your spreadsheet, but if you do, post here just
to
let me know you emailed it.
Cheers,
Jason Lepack
Kevin wrote:
There hundreds of names on each sheet.
Basically each name represents a cell for each port on a switch.
I want to go to have a summary page that will go through each
name and add
the text from that cell to the next line on the summary page.
ex, if sheet1 range(a1) has the name switch1port1 and
switch1port2, I want
to loop through the names and add the text from each of those
cells to a page
called summary. if the text in those 2 cells is Host1 and Host2
then I want
Host1 and Host2 added to the summary page.
I cant just go down the list from each page because the cells on
each switch
page are all over the place as I laid it out to look physically
like the
switch.
Hope that makes sense....
"Jason Lepack" wrote:
What exactly are you trying to do?
The reason it doesn't go into the loop is because there are no
names in
that worksheet.
If you let the group know why you want to do that loop, then
the group
can tell you how to do it.
Cheers,
Jason Lepack
Doug Glancy wrote:
Kevin,
Dim Nam as Name
For each Nam in Activesheet.Names
'yak yak yak
Next Nam
hth,
Doug
"Kevin" wrote in message
...
I want to perform certain tasks for each name in the
activeworksheet. I'm
not
sure how to write the loop.
For Each Name in Activesheet
blah blah
Next
This doesnt work, what would be the correct syntax?