setting sheet as variable

**HarryKlein** · 08-08-2012, 06:32 AM

Hello,

I need to create sheets, and after creating I need a few things to do with them, and I also want to name them.
I have added a variable, nSheet, and I want to use it for the last sheet I created, somehow like this:

dim nSheet as worksheet
for i=lbound(myArray) to ubound(myArray)
    set nsheet=sheets.add after:=sheets(sheets.count) name=myArray(i)
    with nsheet
     .
     .
     end with
next

Unfortunately I cannot figure out, how this line

set nsheet=sheets.add after:=sheets(sheets.count) name=myArray(i)

should look like, maybe it cannot be done in one line.
Could anybody help?

Many Thanks

**NickyC** · 08-08-2012, 06:40 AM

Hi
try two lines:

Set nsheet = Sheets.Add(after:=Sheets(Sheets.Count))
nsheet.Name = myArray(i)

**HarryKlein** · 08-08-2012, 07:05 AM

Thanks, you are right, I have confused myself with the syntax.

**JosephP** · 08-08-2012, 06:40 AM

why do it in one line?

dim nSheet as worksheet
for i=lbound(myArray) to ubound(myArray)
    set nsheet=sheets.add after:=sheets(sheets.count)
    with nsheet
     .name=myArray(i)
     .
     end with
next