Why does this code not work?

R

rk0909

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
 
R

rk0909

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.
 
D

Don Guillett

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
 
F

FSt1

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
 
D

Dave Peterson

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
 
J

Jim Thomlinson

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...
 
G

ghdiez

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
Click to expand...

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.
 
D

Dave Peterson

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.
 
R

rk0909

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
 
D

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.
 
R

rk0909

Got it. thanks much again.

Dave Peterson said:
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.
Click to expand...
 
Top