Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Why does this code not work?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code does not work anymore | Excel Discussion (Misc queries) | |||
Event code won't work | Excel Discussion (Misc queries) | |||
Why this code is not work? | Excel Worksheet Functions | |||
Number Format Code won't work | Excel Discussion (Misc queries) | |||
code is not to work on sheet1 | New Users to Excel |