Create and Name a Worksheet with VBA

February 25, 2002 - by Juan Pablo Gonzalez

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"