Extracting a avialble Tab Name from a workbook

**Naveed Raza** · 11-07-2012, 06:07 AM

Hi All,

From this sites I learn lots of things and thanks Excel Forum by this site v r learning lots of new thing.

I have an issue in my below coding.
My question is -- I make below code to extract available TAB names from a workbook and its working nicely but printing all the TAB Names from a workbook its showing "Script out of range, Run time error - 9" and I am Zero in understanding the Run time error and please explain me what is this run time error - 9 why this number showing as a run time error.

Sub GetShtNam()
Dim ShtNam As String
Dim wsindex As Integer
mrow = 2
wsindex = 1
Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).Select
Selection.ClearContents
Range("A1").Select
For i = 1 To 254
If Worksheets(wsindex).Name = "" Then
Range("A1").Select
Exit Sub
Else
ShtNam = Worksheets(wsindex).Name
Cells(mrow, 1).Value = ShtNam
mrow = mrow + 1
wsindex = wsindex + 1
End If
Next
End Sub

Can anyone please help me out and please explain with single quotation in script page so that i can understand easily.

Thanks in Advance !

Thanks,
Naveed.

**TMS** · 11-07-2012, 06:27 AM

Please Login or Register  to view this content.

Regards, TMS

**TMS** · 11-07-2012, 06:28 AM

Or:

Please Login or Register  to view this content.

Regards, TMS

**Naveed Raza** · 11-08-2012, 03:32 AM

Thanks alot sir ,thnks for ur reply.

I like ur way of making code and please have small review on my below after spending 30mins i successfully figure out the code.

Please have review and please let me know is it 100% accurately work or not ?

Sub GetTabNam()
Dim TabNam As String
Dim wsindex As Integer
mrow = 2
wsindex = 1
wsindex2 = ActiveWorkbook.Worksheets.Count
Range("A2:A" & ActiveSheet.UsedRange.Rows.Count).Select
Selection.ClearContents
Range("A1").Select
For i = wsindex To wsindex2
TabNam = Worksheets(wsindex).Name
Cells(mrow, 1).Value = TabNam
mrow = mrow + 1
wsindex = wsindex + 1
Next
MsgBox "All Available Tab Name Extract ! "
End Sub

Thanks in Advance !

Thanks,
MNR.

**TMS** · 11-08-2012, 04:03 AM

I have, effectively, already reviewed your code and provided two shorter and more efficient versions of code to achieve the required objective.

If your code works, and it does what you want it to do, I'm not sure what you want me to say.

How can I break it gently?

You have variables that you don't really need as you set them and only use them once in the next line, eg, Tabnam. You have separate counters for the worksheet index, the row where you store the Tab Name and the loop counter; you could use just one. And you change the base value of the loop within the loop, that is, wsindex, which is not recommended. Finally, you use ".Select ... .Selection." which is to be avoided.

Please note that, to comply with forum rules, you should add code tags to your code examples.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**Naveed Raza** · 11-08-2012, 04:49 AM

I am extremely SORRY sir,

As i am not that much good in VBA Macro and in which way I learn Macros i tried.

Sorry sir and thanks for pointing out the differents in defining variables and in future i will not make this type mistakes in defining variables.

Once again Thanks for this explanation and Sorry as well.

Thanks - Naveed.

**TMS** · 11-08-2012, 04:58 AM

There's no need to apologise. We all have to learn, and often we learn best by making mistakes. I have to be honest and say I don't like/enjoy learning by my mistakes but, generally, you remember and don't do it again.

I've been a programmer fo 40 years, I still make mistakes and I learn something new pretty much every day.

And I prefer to see someone come to the forum with code they've tried to write and ask for help, rather than just say "I want/need this, can you write it for me?"

So, good luck with your coding ... and don't be put off if it's not right first time

Regards, TMS

PS: please don't forget the code tags and marking the thread as solved.