inserting sheets in excel

[Guest]

I am building an automated macro where depending on a certain conditions a
formatted sheet is inserted and key fields from the master spreadsheet, this
may happen more than once in the spreadsheet. I can do it once, but cannot
find out how to select the last created sheet where I create more than one
sheet so I can insert the default data.

Any help would be appreciated.

thanks

 

[Tom Ogilvy]

When you create it, it will be the activesheet. It might be useful to
assign it a sequential style unique name at that time.

 

[Guest]

Makes sense, but how do I do it?

thanks

Neil

When you create it, it will be the activesheet. It might be useful to
assign it a sequential style unique name at that time.

 

[Dave Peterson]

dim mstrWks as worksheet
dim newWks as worksheet

set mstrwks = activesheet 'worksheets("sheet99")
set newwks = worksheets.add

Then you can use that newwks variable to do things.

newwks.range("a1").value = "Hey, I just added this sheet"

newwks.name = format(now,"yyyymmdd_hhmmss")
'a nice unique name???

 

[Tom Ogilvy]

Dim i as Long
Dim sh as Worksheet
i = 0
for each sh in Worksheets
if lcase(Left(sh.name,12)) = "myaddedsheet" then
i = i + 1
Next
End Sub
worksheets.add After:=worksheets(worksheets.count)
Activesheet.Name = "MyAddedSheet" & i + 1

 

[Jim May]

I had to change lines:

Next
End Sub

to

End If
Next

Right?

 

[Tom Ogilvy]

As Jim said (thanks)

Dim i as Long
Dim sh as Worksheet
i = 0
for each sh in Worksheets
if lcase(Left(sh.name,12)) = "myaddedsheet" then
i = i + 1
end if
Next
worksheets.add After:=worksheets(worksheets.count)
Activesheet.Name = "MyAddedSheet" & i + 1