How to add a sheet after checking its existence?

[prabha_friend]

Like:

If Not Sheets(Temp(0).AbsolutePosition + 1) Then Sheets.Add After:=Sheets(Sheets.Count)

or

If Not Sheets("TestSheet") Then Sheets.Add After:=Sheets(Sheets.Count)

Any other way than for each sheet in activeworkbook.sheets?

Got a 'No' on the following link:
http://www.mrexcel.com/forum/excel-questions/800413-why-piece-code-returning-error.html

 

[kevlray]

I do not know if this idea will work for you. But I recently had to dig through some VBA code for Excel they just copied the sheet to a new sheet.

Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)

 

[Brianwarnock]

Any other way than for each sheet in activeworkbook.sheets?

Got a 'No' on the following link:
http://www.mrexcel.com/forum/excel-questions/800413-why-piece-code-returning-error.html

So why don't you believe them, how can you know whether a sheet exists or not without checking every sheet? Even the frowned on method of using error checking to control the flow relies on the system to check every sheet.

Brian

 

[vbaInet]

Like Brian I don't see why you worry about looping through the collection of sheets to check an existence of one. It's not like you will have 100k sheets in one workbook. Even with that it will still be lightning fast. The only time there isn't a loop is when there's a pointer to the object.

There are cases were optimised code is worthwhile, this one just doesn't need it.

 

[prabha_friend]

Hi Brian and VbaInet, Its not like I don't want to use the loops. I just prefer to use a one-line code if its available. Thanks to your replies.

 

[vbaInet]

Shorter lines of code doesn't always result in greater performance. I don't think about lines of code, I think about which method gives the quickest result and in most cases it ends up being longer lines of code.
The compiler is quick enough to zoom through lines of code, it's the interpretation of the code that matters most.