Range names vs Sheet name

[Hennie Neuhoff]

I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user will
be able to change the sheetname to a more descriptive name (stockitem name).
A macro will sort the sheet names in alphabetically order which will be
easier to select from in a userform. My problem is to perform the stock
issues/received on the specific stock item [the same macro for all activity].
I battle to get this working. I know that I should use range names - but I'm
lost!!

 

[Joel]

I think yo need a unique number to indentify each item. It doesn't need to
be the sheet name but some number on each sheet the uniquely identifies each
item. I wouldn't reliy on the sheet name which is manually changed to
identify each item. Yo could place the number in cell A1 on each sheet or
some specific cell(s).

You can seach eavery sheet ofr the item with a simple loop

FindItem = "abc"
SheetName = ""
for each sht in sheets
if sht.Range("A1") = FindItem then
SheetName = sht.name
exit for
end if
next sht
if SheetName = "" then
msgbox("Could Not find Item : " & FindItem)
else
'enter You code here
end if

 

[Don Guillett]

Try this.
Sub listandsortsheetnames()
For i = 1 To ActiveWorkbook.Sheets.Count
'MsgBox Sheets(i).Name
Cells(i, 1) = Sheets(i).Name

Columns("A").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, MatchCase:=False, Orientation:=xlTopToBottom
Next i
End Sub

 

[Otto Moehrbach]

Hennie
It's a little difficult to follow what you have and what you want Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure of
what you want done. Pretend that you have to do it manually, with no macro.
What would you do, step-by-step? HTH Otto

 

[Hennie Neuhoff]

Otto, tks for your help - here goes. I would like the user to assign his own
name to the sheet, each sheet contains the stock movement of the specific
stock, ie. receipts, issues and stock losses etc. The sheets can then be sort
in a alphab. list that will be used in the userform. Whenever there is a
stock movement he will select the stocksheet the macro will then update the
specific sheet. In other words irrespective the name the user assigns to the
sheet I would like a unique "name" [range name?] to identyf and activated the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN

Hennie
It's a little difficult to follow what you have and what you want Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure of
what you want done. Pretend that you have to do it manually, with no macro.
What would you do, step-by-step? HTH Otto

I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user
will
be able to change the sheetname to a more descriptive name (stockitem
name).
A macro will sort the sheet names in alphabetically order which will be
easier to select from in a userform. My problem is to perform the stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names - but
I'm
lost!!

 

[Gary Keramidas]

maybe you can use the codename of the sheet.

for example: if you rename sheet2 to "test"

activesheet.name will return "test"
but
activesheet.codename will return "sheet2"

in the vbeditor, click view, then properties window, or press F4

select a sheet under microsoft excel objects.
the codename is the first entry

--


Gary

Otto, tks for your help - here goes. I would like the user to assign his own
name to the sheet, each sheet contains the stock movement of the specific
stock, ie. receipts, issues and stock losses etc. The sheets can then be sort
in a alphab. list that will be used in the userform. Whenever there is a
stock movement he will select the stocksheet the macro will then update the
specific sheet. In other words irrespective the name the user assigns to the
sheet I would like a unique "name" [range name?] to identyf and activated the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN

Hennie
It's a little difficult to follow what you have and what you want Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure of
what you want done. Pretend that you have to do it manually, with no macro.
What would you do, step-by-step? HTH Otto

I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user
will
be able to change the sheetname to a more descriptive name (stockitem
name).
A macro will sort the sheet names in alphabetically order which will be
easier to select from in a userform. My problem is to perform the stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names - but
I'm
lost!!

 

[Otto Moehrbach]

Gary
Given that sheet "MySheet" has a code name of Sheet5, how would I write
the macro statement to select MySheet by citing the code name? Thanks for
your time and I learn something new every day. Otto

maybe you can use the codename of the sheet.

for example: if you rename sheet2 to "test"

activesheet.name will return "test"
but
activesheet.codename will return "sheet2"

in the vbeditor, click view, then properties window, or press F4

select a sheet under microsoft excel objects.
the codename is the first entry

--


Gary

Otto, tks for your help - here goes. I would like the user to assign his
own
name to the sheet, each sheet contains the stock movement of the specific
stock, ie. receipts, issues and stock losses etc. The sheets can then be
sort
in a alphab. list that will be used in the userform. Whenever there is a
stock movement he will select the stocksheet the macro will then update
the
specific sheet. In other words irrespective the name the user assigns to
the
sheet I would like a unique "name" [range name?] to identyf and activated
the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN

Hennie
It's a little difficult to follow what you have and what you want
Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure
of
what you want done. Pretend that you have to do it manually, with no
macro.
What would you do, step-by-step? HTH Otto
message
I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user
will
be able to change the sheetname to a more descriptive name (stockitem
name).
A macro will sort the sheet names in alphabetically order which will
be
easier to select from in a userform. My problem is to perform the
stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names -
but
I'm
lost!!

 

[Otto Moehrbach]

Hennie
Say that you named cell A1 of every sheet some distinctive name. Say
that you want to select the sheet that has A1 named "Doodle".
The statement Range("Doodle").Parent.Name will return the tab name of the
sheet that has a cell named "Doodle". Therefore, the statement:
Sheets(Range("Doodle").Parent.Name).Select will select the sheet you want.
Is this what you want? HTH Otto

Otto, tks for your help - here goes. I would like the user to assign his
own
name to the sheet, each sheet contains the stock movement of the specific
stock, ie. receipts, issues and stock losses etc. The sheets can then be
sort
in a alphab. list that will be used in the userform. Whenever there is a
stock movement he will select the stocksheet the macro will then update
the
specific sheet. In other words irrespective the name the user assigns to
the
sheet I would like a unique "name" [range name?] to identyf and activated
the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN

Hennie
It's a little difficult to follow what you have and what you want
Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure of
what you want done. Pretend that you have to do it manually, with no
macro.
What would you do, step-by-step? HTH Otto
message

I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user
will
be able to change the sheetname to a more descriptive name (stockitem
name).
A macro will sort the sheet names in alphabetically order which will be
easier to select from in a userform. My problem is to perform the stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names -
but
I'm
lost!!

 

[Gary Keramidas]

you would just use the codename

sheet2.select

--


Gary

Gary
Given that sheet "MySheet" has a code name of Sheet5, how would I write the
macro statement to select MySheet by citing the code name? Thanks for your
time and I learn something new every day. Otto

maybe you can use the codename of the sheet.

for example: if you rename sheet2 to "test"

activesheet.name will return "test"
but
activesheet.codename will return "sheet2"

in the vbeditor, click view, then properties window, or press F4

select a sheet under microsoft excel objects.
the codename is the first entry

--


Gary

Otto, tks for your help - here goes. I would like the user to assign his own
name to the sheet, each sheet contains the stock movement of the specific
stock, ie. receipts, issues and stock losses etc. The sheets can then be
sort
in a alphab. list that will be used in the userform. Whenever there is a
stock movement he will select the stocksheet the macro will then update the
specific sheet. In other words irrespective the name the user assigns to the
sheet I would like a unique "name" [range name?] to identyf and activated
the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN


:

Hennie
It's a little difficult to follow what you have and what you want Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure of
what you want done. Pretend that you have to do it manually, with no
macro.
What would you do, step-by-step? HTH Otto
I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user
will
be able to change the sheetname to a more descriptive name (stockitem
name).
A macro will sort the sheet names in alphabetically order which will be
easier to select from in a userform. My problem is to perform the stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names - but
I'm
lost!!

 

[Otto Moehrbach]

Thanks Gary. Otto

you would just use the codename

sheet2.select

--


Gary

Gary
Given that sheet "MySheet" has a code name of Sheet5, how would I
write the macro statement to select MySheet by citing the code name?
Thanks for your time and I learn something new every day. Otto

maybe you can use the codename of the sheet.

for example: if you rename sheet2 to "test"

activesheet.name will return "test"
but
activesheet.codename will return "sheet2"

in the vbeditor, click view, then properties window, or press F4

select a sheet under microsoft excel objects.
the codename is the first entry

--


Gary


message Otto, tks for your help - here goes. I would like the user to assign
his own
name to the sheet, each sheet contains the stock movement of the
specific
stock, ie. receipts, issues and stock losses etc. The sheets can then
be sort
in a alphab. list that will be used in the userform. Whenever there is
a
stock movement he will select the stocksheet the macro will then update
the
specific sheet. In other words irrespective the name the user assigns
to the
sheet I would like a unique "name" [range name?] to identyf and
activated the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN


:

Hennie
It's a little difficult to follow what you have and what you want
Excel
to do. You have 150 sheets and the sheet names can be anything the
user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure
of
what you want done. Pretend that you have to do it manually, with no
macro.
What would you do, step-by-step? HTH Otto
message
I'm suppliying various branches a standard workbook for stock
control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The
user
will
be able to change the sheetname to a more descriptive name
(stockitem
name).
A macro will sort the sheet names in alphabetically order which will
be
easier to select from in a userform. My problem is to perform the
stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names -
but
I'm
lost!!

 

[Hennie Neuhoff]

Otto,
Tks very much! That will do the trick. The problem comes with the sorting -
if I don't sort the sheets its easy.
Thanks again for your help.
--
HJN

Hennie
Say that you named cell A1 of every sheet some distinctive name. Say
that you want to select the sheet that has A1 named "Doodle".
The statement Range("Doodle").Parent.Name will return the tab name of the
sheet that has a cell named "Doodle". Therefore, the statement:
Sheets(Range("Doodle").Parent.Name).Select will select the sheet you want.
Is this what you want? HTH Otto

Otto, tks for your help - here goes. I would like the user to assign his
own
name to the sheet, each sheet contains the stock movement of the specific
stock, ie. receipts, issues and stock losses etc. The sheets can then be
sort
in a alphab. list that will be used in the userform. Whenever there is a
stock movement he will select the stocksheet the macro will then update
the
specific sheet. In other words irrespective the name the user assigns to
the
sheet I would like a unique "name" [range name?] to identyf and activated
the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN

Hennie
It's a little difficult to follow what you have and what you want
Excel
to do. You have 150 sheets and the sheet names can be anything the user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure of
what you want done. Pretend that you have to do it manually, with no
macro.
What would you do, step-by-step? HTH Otto
message
I'm suppliying various branches a standard workbook for stock control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The user
will
be able to change the sheetname to a more descriptive name (stockitem
name).
A macro will sort the sheet names in alphabetically order which will be
easier to select from in a userform. My problem is to perform the stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names -
but
I'm
lost!!

 

[Otto Moehrbach]

Hennie
Do you mean that you want some code to sort the sheets? Here is a macro
I have used for sheet sorting. HTH Otto
Sub ArrangeSheetsAlphabetically()
'Leo Heuser, 19. Nov. 2002
Dim Counter As Long
Dim Counter1 As Long
For Counter = 1 To Sheets.Count - 1
For Counter1 = Counter + 1 To Sheets.Count
If Sheets(Counter1).Name < Sheets(Counter).Name Then
Sheets(Counter1).Move before:=Sheets(Counter)
Sheets(Counter + 1).Move before:=Sheets(Counter1)
End If
Next Counter1
Next Counter
End Sub

Otto,
Tks very much! That will do the trick. The problem comes with the
sorting -
if I don't sort the sheets its easy.
Thanks again for your help.
--
HJN

Hennie
Say that you named cell A1 of every sheet some distinctive name. Say
that you want to select the sheet that has A1 named "Doodle".
The statement Range("Doodle").Parent.Name will return the tab name of the
sheet that has a cell named "Doodle". Therefore, the statement:
Sheets(Range("Doodle").Parent.Name).Select will select the sheet you
want.
Is this what you want? HTH Otto
message

Otto, tks for your help - here goes. I would like the user to assign
his
own
name to the sheet, each sheet contains the stock movement of the
specific
stock, ie. receipts, issues and stock losses etc. The sheets can then
be
sort
in a alphab. list that will be used in the userform. Whenever there is
a
stock movement he will select the stocksheet the macro will then update
the
specific sheet. In other words irrespective the name the user assigns
to
the
sheet I would like a unique "name" [range name?] to identyf and
activated
the
sheet the user selects from the userform.
Is that more clear?
Thanks again
--
HJN


:

Hennie
It's a little difficult to follow what you have and what you want
Excel
to do. You have 150 sheets and the sheet names can be anything the
user
chooses, so the macro cannot use the sheet names as search criteria.
Perhaps it would be helpful if you write down a step-by-step procedure
of
what you want done. Pretend that you have to do it manually, with no
macro.
What would you do, step-by-step? HTH Otto
message
I'm suppliying various branches a standard workbook for stock
control.
Currently the sheets are name Stock1, Stock2 etc to Stock150. The
user
will
be able to change the sheetname to a more descriptive name
(stockitem
name).
A macro will sort the sheet names in alphabetically order which will
be
easier to select from in a userform. My problem is to perform the
stock
issues/received on the specific stock item [the same macro for all
activity].
I battle to get this working. I know that I should use range names -
but
I'm
lost!!