How to find No of sheets ,sheet no
 

Hi ,
Is there any option to find the no of sheets in the work book and any
possible to find the "sheets no" or sheets position

for example: In my workbook contains worksheets

Traffic2005,Customer,Tax2005,Mar 2006

Instead of using sheets name, i want to use the sheets's no
like
Traffic2005 - "1"
Customer- "2"
Tax2005-"3"
Mar 2006-"4"

Thanks in advance for your reply.

Have a nice day.

How to find No of sheets ,sheet no
 

Your have a couple of choices:

Dim i As Long

For i = 1 To ThisWorkbook.Worksheets.Count
Debug.Print ThisWorkbook.Worksheets(i).Name
Next

'or
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
Debug.Print WS.Name
Next

NickHK

"Vadhimoo" wrote in message
...
Hi ,
Is there any option to find the no of sheets in the work book and any
possible to find the "sheets no" or sheets position

for example: In my workbook contains worksheets

Traffic2005,Customer,Tax2005,Mar 2006

Instead of using sheets name, i want to use the sheets's no
like
Traffic2005 - "1"
Customer- "2"
Tax2005-"3"
Mar 2006-"4"

Thanks in advance for your reply.

Have a nice day.

How to find No of sheets ,sheet no
 

Hi Vadhimoo

You can use the index

Sheets(1).Range("A1").Value

But if people change the order this will give strange results<g

You can also use the code name to avoid this problem

In the VBA editor you see
Sheet1(Sheet1)

You can use
Sheet1.Range("A1").Value

You can change the code name in the properties


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Vadhimoo" wrote in message ...
Hi ,
Is there any option to find the no of sheets in the work book and any
possible to find the "sheets no" or sheets position

for example: In my workbook contains worksheets

Traffic2005,Customer,Tax2005,Mar 2006

Instead of using sheets name, i want to use the sheets's no
like
Traffic2005 - "1"
Customer- "2"
Tax2005-"3"
Mar 2006-"4"

Thanks in advance for your reply.

Have a nice day.