Macro to find the sheet name

**kishoremcp** · 09-12-2012, 11:43 PM

hi,
I have the below macro which is working fine. But the problem is it is only taking as it is in the sheet name. I mean it case sensitive. If I give pa02 instead of PA02 it is telling no sheets. It should not be line that. it should work either if i type in small letters or caps letters or the combination. One more thing i want is, if i have multiple sheets which have 02 it should to to the next sheet which contains the string 02. I mean i need next button as well. It should be floating on every sheet so that i can search for the sheet on every sheet and go to the required sheet. I hope I am clear.
Please hlep.

Please Login or Register  to view this content.

**JBeaucaire** · 09-13-2012, 12:57 AM

I don't see any reason this won't work, I've tested it on my system and it activates the correct sheets regardless of capitalization.

Please Login or Register  to view this content.

**kishoremcp** · 09-13-2012, 01:17 AM

Thank you for your reply. I tried the macro which you gave now. But if i am finding a part of the sheet name it is not working.
I mean if the sheet name is 20144_OOW and if i am searching for 20144 it says "sheet doesnot exists" and one more thing is if i have another sheet which has 2014(Ex: 20144 error) then it should give me another button "Next" by clicking on it should take me to the next 20144 containing sheet. Pleae help....

**kishoremcp** · 09-13-2012, 03:39 AM

Please help

**mnjofra** · 09-13-2012, 04:53 AM

hi kishoremcp,
try this code...

Please Login or Register  to view this content.

regards,

**kishoremcp** · 09-13-2012, 06:15 AM

No, it is not working

**mnjofra** · 09-13-2012, 06:41 AM

Hi again,
if you have upper/lower case problem, following code should be fix:

Please Login or Register  to view this content.

This procedure you have to place in Module1 object if you need to trigger macro from any sheet.
It's work perfectly in my Excel 2003.

Regards,

**kishoremcp** · 09-13-2012, 08:20 AM

It is working but if i have jam in another sheet name it is not going to that pirticular sheet. it stops at the first page

**kishoremcp** · 09-14-2012, 03:35 AM

Any more help

**JBeaucaire** · 09-14-2012, 10:08 AM

What other help do you need?

**kishoremcp** · 11-08-2012, 10:05 AM

The below macro is working for some extent. I mean if i have two sheets 144 and 20144 then it is searching for 144 only and if i again search for 144 it only shows one sheet only that is 144 but it will not show you 20144.so i want another button where i can click on next which will find the next sheet name which should show me the sheet 20144 as it also contains 144. At the same time i am also having the upper case and lower case problem while searching.Is it possible to avoid that also. Please help me. Attaching the file also again.

Please Login or Register  to view this content.

**jindon** · 11-08-2012, 10:36 AM

What do you mean by "Show" ?

Please Login or Register  to view this content.

**kishoremcp** · 11-09-2012, 03:43 AM

Yes. this is working good for upper case and lower case. Now for my second question which is about the second sheet with same name followed by some different extension. Like (20144 is a sheet and 144 error is another sheet) if i search for 144 it will give either 20144 or 144. What if i want to go with the next sheet other than the selected one ( i mean if is shows 144 errors sheet if i search for 144 and i want to go for 20144 then what need to be done). i hope i am clear. please help me.

**jindon** · 11-09-2012, 04:22 AM

No idea about what you are asking.

Wait for someone else to come in.

**kishoremcp** · 11-09-2012, 04:47 AM

If I run the above macro I am having two buttons. One is OK and the other is Cancel. I want to have another button with name as Next and it should search the next sheet name with the same search string which i enter. I think i am clear now

**kishoremcp** · 11-09-2012, 06:12 AM

Any suggestions

**kishoremcp** · 11-09-2012, 07:37 AM

I am looking for help

**kishoremcp** · 11-10-2012, 10:55 AM

Still looking for help

**oeldere** · 11-10-2012, 11:35 AM

I'm not an VBA export.

When I run the macro you provided I get the right worksheet.

If I input an anvalid value (e.g. 2014) it finds the right worksheet (20144)

It only finds 1 worksheet while running the macro, but that is what you want (i suppose).

So, i can understand that you have no other reaction, because to my opinion you have the solution you want,

Or you are not clear what you want (so other members cant help you).

**HaHoBe** · 11-10-2012, 12:12 PM

Hi, kishoremcp,

maybe you have a look at this approach which uses a VBA-Userform with one Textbox (for the search), one Listbox for the display of either all or searched sheetnames and three buttons (one for searching or restoring the sheetnames, one for activating a selected sheet, one to close the form).

Code behind the Userform:

Please Login or Register  to view this content.

Code is started by opening the workbook via Workbook_Open:

Please Login or Register  to view this content.

Right now the sheetname of the one activated will be deleted from the listbox (may be restored via SheetNames).

HTH,
Holger

**HaHoBe** · 11-10-2012, 01:20 PM

Hi, kishoremcp,,

please change the MultiSelect for the Listbox from 1 - frmMultiSelectMulti to 0 - frmMultiSelectSingle (I missed that one). There won´t be a runtime error but you will be led to the sheet which was the last to be selected. By changing the Multiselect there will only one choice available.

Ciao,
Holger

Macro to find the sheet name

LinkBack

Thread Tools

Rate This Thread

Display

Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Re: Macro to find the sheet name

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions