How can I add a new sheet into workbook alphabetically using VBA?

[Apollo6]

I have an existing workbook that contains a seperate sheet for employees. I am able to write a macro that inserts a new sheet and re-names it to what I want. And I can also have it put the sheet where I know it should be but the VBA code references the actual sheet number(s) that it places it before/after. The next time I add a new sheet, the code would be off by a sheet because of the second new sheet.

I want to be able to loop through all the sheets and logically find where it should go.

Any suggestions or ideas are appreciated.

 

[RobBroekhuis]

i=1
do while sheets(i).name<NewName and i<=sheets.count
i=i+1
loop

At the end of the loop, i will be the desired index for the new sheet. Note that you may need to treat i=1 or i=sheets.count+1 separately.
Rob

 

[Apollo6]

Do I need to Dim i as Index? I'm getting an Object Variable or With Block variable not set... Still working on it.

 

[RobBroekhuis]

Dim i as Integer
The object variable error is probably because the Sheets property is not available from where you are trying to run the code. You could be more specific and make it
activeworkbook.sheets or thisworkbook.sheets

Rob

 

[Apollo6]

Rob:

Thanks for the tip. A little modification and it's working fine.