How to get the available sheet name
 

Hi,
I need to add a new sheet thru macros which i have done. But now
need to know the sheets name that alreday been existing.
I got the sheets.count. But i am not getting the name of the sheet.

How to know this.
Can any one help me to reach this

Thanks
Venkatesh

--
Message posted from http://www.ExcelForum.com

How to get the available sheet name
 

Venkatesh,

You have two choices AFAICS, that is loope through all sheets and put the
name into an array, and check your proposed name against that, or try and
create it, and if it exists, try with a new name.

Here is some code to test if it exists

Function SheetExists(Filename As String)
Dim oSh As Worksheet
On Error Resume Next
Set oSh = ActiveWorkbook.Worksheets(Filename)
On Error GoTo 0
SheetExists = Not oSh Is Nothing
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kvenku " wrote in message
...
Hi,
I need to add a new sheet thru macros which i have done. But now i
need to know the sheets name that alreday been existing.
I got the sheets.count. But i am not getting the name of the sheet.

How to know this.
Can any one help me to reach this

Thanks
Venkatesh.


---
Message posted from http://www.ExcelForum.com/

How to get the available sheet name
 

I need to know the list of sheet name which are available..example
Sheet1,Sheet2,Sheet3...like that

How to add a new sheet with a name given my me thru text box in the
VBA.

It would be very helpful if you do this for me

Thanks
venkatesh.


---
Message posted from http://www.ExcelForum.com/

How to get the available sheet name
 

The available sheet names is infinite, because they can be whatever you
want.

If yoiu names are all Sheet1,2, 3, 4 format, try this

iNext = Activeworkbook.Worksheets.Count + 1
Do Until Not SheetExists("Sheet" & iNext)
iNext = iNext + 1
Loop
Woeksheets.Add.Name = "Sheet" & iNext



Function SheetExists(Filename As String)
Dim oSh As Worksheet
On Error Resume Next
Set oSh = ActiveWorkbook.Worksheets(Filename)
On Error GoTo 0
SheetExists = Not oSh Is Nothing
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kvenku " wrote in message
...
I need to know the list of sheet name which are available..example
Sheet1,Sheet2,Sheet3...like that

How to add a new sheet with a name given my me thru text box in the
VBA.

It would be very helpful if you do this for me

Thanks
venkatesh.


---
Message posted from http://www.ExcelForum.com/

How to get the available sheet name
 

My Sheet may contain any name is sheet not sheet1.2.3...

In my case i need to populate the available sheet names into th
listbox. Is there any way to find that .

Please reply me faster

Thanks

Venkatesh

--
Message posted from http://www.ExcelForum.com

How to get the available sheet name
 

If I understand what you are saying, here's an example:

Sub PopulateListBox()
Dim ws As Worksheet
For Each ws In Worksheets
ListBox1.AddItem ws.Name
Next
End Sub

--

Vasant


"kvenku " wrote in message
...
My Sheet may contain any name is sheet not sheet1.2.3...

In my case i need to populate the available sheet names into the
listbox. Is there any way to find that .

Please reply me faster

Thanks

Venkatesh.


---
Message posted from http://www.ExcelForum.com/

How to get the available sheet name
 

Then you will have to the other way around.

Get the value from the text box, test if the sheet already exists, if not
create it, if so, error and tell the user.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"kvenku " wrote in message
...
My Sheet may contain any name is sheet not sheet1.2.3...

In my case i need to populate the available sheet names into the
listbox. Is there any way to find that .

Please reply me faster

Thanks

Venkatesh.


---
Message posted from http://www.ExcelForum.com/