All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

try

worksheets(wks).activate


"rk0909" wrote:

All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

this doens't work.

This would work if tab name is "Sheet 1", if the tab name is changed to
something else it wouldnt work.



"Duke Carey" wrote:

try

worksheets(wks).activate


"rk0909" wrote:

All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Try
Sub selectsheet()
n = 3
Sheets(n).Select
End Sub

but why select
n = 3
'Sheets(n).Select
MsgBox Sheets(n).Range("k1")
or
Sheets(n).Range("k1")=date

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"rk0909" wrote in message
...
All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Dim wks as object
dim myCodeName as string
dim mySheet as object

set mysheet = nothing
mycodename = "Sheet1"
for each wks in activeworkbook.sheets
if lcase(wks.codename) = lcase(mycodename) then
set mysheet = wks
'stop looking
exit for
end if
next wks

if mysheet is nothing then
msgbox "Not found!"
else
mysheet.select
end if



rk0909 wrote:

All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

--

Dave Peterson

hi
1. you haven't declared what wks is.
2. you haven''t set as an object

try
Dim n As Integer
Dim wks As Worksheet
Set wks = Sheets("sheet1")
n = 1
wks.Name = "sheet" & n

wks.Select

regards
FSt1
"rk0909" wrote:

All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

In your code you do not declare wks so it is a variant. When you execute
wks = "sheet" & n
you make wks into a string with the value sheet1 in it.
You then try ot use the string as an object which will not work. You will
gete a 424 object required error...

Try this...
Sub test()
Dim wks As Worksheet
Dim l As Long

l = 1
For Each wks In Worksheets
If wks.CodeName = "Sheet" & l Then Exit For
Next wks
MsgBox wks.CodeName
End Sub

That being said I highly recommend that you not use this code as it is IMO
not a great idea. While the end user can not change the code names of the
sheets you can still run into a problem. Delete the sheet with code name
Sheet1. Save the file and close it. Open it and create a new sheet. The new
sheet will have code name Sheet1. You are best off to rename the code names
of the sheets to avoid this problem... If you Delete a sheet with code name
shtMySheet it will never get recreated with that same code name...
--
HTH...

Jim Thomlinson


"rk0909" wrote:

All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Thanks all for your help. Much appreciated.

RK.

"Jim Thomlinson" wrote:

In your code you do not declare wks so it is a variant. When you execute
wks = "sheet" & n
you make wks into a string with the value sheet1 in it.
You then try ot use the string as an object which will not work. You will
gete a 424 object required error...

Try this...
Sub test()
Dim wks As Worksheet
Dim l As Long

l = 1
For Each wks In Worksheets
If wks.CodeName = "Sheet" & l Then Exit For
Next wks
MsgBox wks.CodeName
End Sub

That being said I highly recommend that you not use this code as it is IMO
not a great idea. While the end user can not change the code names of the
sheets you can still run into a problem. Delete the sheet with code name
Sheet1. Save the file and close it. Open it and create a new sheet. The new
sheet will have code name Sheet1. You are best off to rename the code names
of the sheets to avoid this problem... If you Delete a sheet with code name
shtMySheet it will never get recreated with that same code name...
--
HTH...

Jim Thomlinson


"rk0909" wrote:

All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

On 4 sep, 10:06, rk0909 wrote:
All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. *Any help will be appreciated.

Thanks much,

RK

Sub test()
* * Dim n As Integer

* * n = 1
* * wks = "sheet" & n

* * wks.Select
End Sub

Hi Rk:

You need to do some changes:

1. wks = "sheet" & Trim(Str(n))
This, becuase the n variable is numeric (integer) and the string
representation has a space at left. You should remove the space.

2. Worksheets(wks).Select
The reason is that wks is a string variable, not an object. You pass
the sheet name (the value of wks variable) to the Worksheets
collection.

Just a note about your first suggestion:

1. wks = "sheet" & Trim(Str(n))

The Str function actually introduces that space character.
wks = "Sheet" & n
would would nicely--it wouldn't include any spaces.



wrote:

On 4 sep, 10:06, rk0909 wrote:
All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Hi Rk:

You need to do some changes:

1. wks = "sheet" & Trim(Str(n))
This, becuase the n variable is numeric (integer) and the string
representation has a space at left. You should remove the space.

2. Worksheets(wks).Select
The reason is that wks is a string variable, not an object. You pass
the sheet name (the value of wks variable) to the Worksheets
collection.

--

Dave Peterson

Dave.
somehow this does not work. "Sheet" & n does not work at all.

If I use Sheets(n). It refers to sheet tab name instead of code name.

Any suggestions will be appreciated.

RK
"Dave Peterson" wrote:

Just a note about your first suggestion:

1. wks = "sheet" & Trim(Str(n))

The Str function actually introduces that space character.
wks = "Sheet" & n
would would nicely--it wouldn't include any spaces.



wrote:

On 4 sep, 10:06, rk0909 wrote:
All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Hi Rk:

You need to do some changes:

1. wks = "sheet" & Trim(Str(n))
This, becuase the n variable is numeric (integer) and the string
representation has a space at left. You should remove the space.

2. Worksheets(wks).Select
The reason is that wks is a string variable, not an object. You pass
the sheet name (the value of wks variable) to the Worksheets
collection.

--

Dave Peterson

I was just commenting on ghdiez's first suggestion.

I didn't mean to suggest that his second suggestion would do what you wanted. I
would loop through the sheets using the code that I previously posted.

rk0909 wrote:

Dave.
somehow this does not work. "Sheet" & n does not work at all.

If I use Sheets(n). It refers to sheet tab name instead of code name.

Any suggestions will be appreciated.

RK
"Dave Peterson" wrote:

Just a note about your first suggestion:

1. wks = "sheet" & Trim(Str(n))

The Str function actually introduces that space character.
wks = "Sheet" & n
would would nicely--it wouldn't include any spaces.



wrote:

On 4 sep, 10:06, rk0909 wrote:
All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Hi Rk:

You need to do some changes:

1. wks = "sheet" & Trim(Str(n))
This, becuase the n variable is numeric (integer) and the string
representation has a space at left. You should remove the space.

2. Worksheets(wks).Select
The reason is that wks is a string variable, not an object. You pass
the sheet name (the value of wks variable) to the Worksheets
collection.

--

Dave Peterson


--

Dave Peterson

Got it. thanks much again.

"Dave Peterson" wrote:

I was just commenting on ghdiez's first suggestion.

I didn't mean to suggest that his second suggestion would do what you wanted. I
would loop through the sheets using the code that I previously posted.

rk0909 wrote:

Dave.
somehow this does not work. "Sheet" & n does not work at all.

If I use Sheets(n). It refers to sheet tab name instead of code name.

Any suggestions will be appreciated.

RK
"Dave Peterson" wrote:

Just a note about your first suggestion:

1. wks = "sheet" & Trim(Str(n))

The Str function actually introduces that space character.
wks = "Sheet" & n
would would nicely--it wouldn't include any spaces.



wrote:

On 4 sep, 10:06, rk0909 wrote:
All,

I am trying to use the code name of sheets instead of the tab name, but it
wouldn't work for me. Any help will be appreciated.

Thanks much,

RK

Sub test()
Dim n As Integer

n = 1
wks = "sheet" & n

wks.Select
End Sub

Hi Rk:

You need to do some changes:

1. wks = "sheet" & Trim(Str(n))
This, becuase the n variable is numeric (integer) and the string
representation has a space at left. You should remove the space.

2. Worksheets(wks).Select
The reason is that wks is a string variable, not an object. You pass
the sheet name (the value of wks variable) to the Worksheets
collection.

--

Dave Peterson


--

Dave Peterson