Create and name a new worksheet with VBA

 

Past Tip of the Day

 

Anupam asks "I am trying to create and Name a sheet from within a Excel Macro. If I use the function Sheets.Add it just names it something like Sheet2 etc naming it greater then the last sheet created. So I cannot access this sheet from within the same macro. Is there a way to Create and name a worksheet at the same time."

Well, the first thing, is use Objects. This is a recommendation, you can live without using them, but, life becomes so much easier when you do...

Just like this:

Dim WS as Worksheet
Set WS = Sheets.Add

You don't have to know where is it located, what's it's name, you just refer to it as WS.

If you still want to do this the "old fashioned" way, try this:

Sheets.Add.Name = "Test"

By Juan Pablo Gonzalez on 25-Feb-2002
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.