code names 1

[topgrill]

hi
i am writing a macro that takes data from some sheets and colates it into one sheet. at the moment i am using
Sheets(3).Select
style of navigation, to move between sheets. i would prefer to use code names as they are a bit less prone to human error, but i am having getting to syntax right to select sheets.
thanks

 

[ChrisBurch]

If by codenames you mean the sheet tab names, then use

sheets("my sheetname&quot.select

Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[xlbo]

To use the codename, juat use
Sheet1.select

where the codename is Sheet1

msgbox Sheet1.name & " " & Sheet1.codename

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[topgrill]

thanks
but i think this is the crux of the problem,
sheet1.select is not working.
there is a sheet with code name sheet1.
when i use this i get "Run-time error '424' object required"
any clues?

 

[combo]

You probably have no sheet with code name Sheet1.
The code name is what you see in the project explorer just after the sheet's icon, i.e. if you have Sheet2(Sheet1), the code namw is Sheet2.
To test active sheet's code name, use:
[tt]Sub GetCodeName()
Msgbox ActiveSheet.CodeName
End Sub[/tt]

combo

 

[topgrill]

no there is definately a sheet code name sheet1. i have just used
MsgBox ActiveSheet.CodeName
to double check and it is there.

 

[xlbo]

Sorry but I have to disagree - a 424 error is the error you get when trying to reference a codename that isn't there.
When you see something like:
Sheet2 (Sheet1)
the code name is the one NOT in brackets
If you are saying that msgbox activesheet.codename
gives Sheet1, check whether there are any spaces at the start or end - this'll make a difference too

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[combo]

Do you have with code and sheets in one workbook?
When you write Sheet1. in the code pane, do you have a drop-down with suggestions (should be)?

combo

 

[topgrill]

there are no spaces before or after sheet1. When i write Sheet1. there is a drop down menue.
when i run sheet1.select i get the error
run-time error '1004'
method'select' of object'_Worksheet' failed

 

[xlbo]

Is the sheet "VISIBLE" ??
needs to be visible to be selected

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[topgrill]

what do you mean by "VISIBLE"? i do not have any trouble selecting the sheet by other means in the VBA code (using Sheets(1).Select of sheets("mysheet&quot.select)
next to visible in the properties for sheet1 it says -1 -xlSheetVisible

 

[xlbo]

Well, if you have a sheet that has a codename of sheet1 and it is visible (ie not hidden), then
Sheet1.select
is entirely the correct syntax
What EXACTLY do you see in the VBA Project window
eg for the 1st sheet in my blank workbook I see:
Sheet1 (Sheet1)

The reason I'm asking all this is that you have given 2 DIFFERENT error messages to us
the 424 error indicates that there is no object called the name that you are trying to select
the 1004 error indicates that there may be an object called sheet1 but for some reason, it cannot be selected (most common cause of this is that it is a HIDDEN sheet - hence me asking about the visibility)

The only other thing I can think of is that you are working with multiple workbooks and you haven't specified which workbook to select Sheet1 from.......

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[topgrill]

you are right. i got the 424 message when i was trying to get my sheet2 and 1004 when i was trying to get sheet1.

but i think the problem was that i was writing from personal.xls file. i have moved the macro to the same file as the one i was running it on and it now seems to work.
wich leads to the next problem. he macro needs to be run from outside the workbook. so how do i specify the workbook.

many thanks
greg

 

[xlbo]

Workbooks("Workbookname&quot.activate
Sheet1.select

should do what you need

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[rodimon]

As outlined in an earlier post you have to have the sheet in the workbook and this will work:

MsgBox ActiveSheet.CodeName 'gets the code of the current sheet but that's not what you want you need to select the sheet by its internal application name the name next to the sheet in the code view window. If the sheet you are looking for is not there you will get the general error message "424 object required". To be safe I'd parse my sheets collection befor I tried to select it.

doing this is just error pron.
Sheet2.Select

The fellow from Tennesse you are wrong as usual