Automate Excel Question

I need to do a few things....
I am creating a new workbook (no problem)
I need to add Sheets after sheet3

Dim O As Object
Set O = CreateObject("excel.application")
O.Visible = True
O.Workbooks.Add
O.sheets.Add

Ok this is fine...but How do I make it add the sheet after Sheet3? (this way adds it before sheet1)

Also...how Do I rename the sheets? (Or add sheet with new name)

Thanks in Advance!

(I will have more questions later...)

Try something like this:

Code:

---

O.Sheets.Add

'Change the name of the new sheet
ActiveSheet.Name = "NewSheetName"

'Move to the end
ActiveSheet.Move After:=Sheets(Sheets.Count)

---

Hi,
I can solve half of your problem ie, iam giving you the code for adding the sheet at the end but i don't know how to rename it..Sorry for that

Here is the code for adding the sheet at the end

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)

Happy Programming

Regards

Great! That work (or close enough on the move one...I figured it out)
Now...DO you know how to set the active sheet?

When you select (activate) a sheet, the ActiveSheet objects references the currently activated sheet.

Example:

Code:

---

Sheets("Sheet1").Select
ActiveSheet.Name = "NewSheetName"

'does the same as

Sheets("Sheet1").Name = "NewSheetName"

The ActiveSheet object is easier to use because you don't have to use the sheet name in every call.

---

Thanks!!!