Problem with inserting new sheets

[bullionbuks]

For some reason when I insert a new sheet into a workbook 3 sheets show up. How can I change the default insert back to 1 sheet at a time.

Thanks,

 

[bullionbuks]

Runtime Error 91

Object Variable or With Block Variable Not Set

 

[Joe4]

My guess is that you are not starting out on the same sheet you started on when you recorded the code. Try this variation:

Sub Insert_Sheet_Ex()
'
' Insert_Sheet_Ex Macro
'
    Dim OrigSheetCount As Integer
    Dim FinalSheetCount As Integer

    OrigSheetCount = Sheets.Count
 
    Sheets(1).Activate
    Sheets.Add Type:="Worksheet"

    FinalSheetCount = Sheets.Count

    MsgBox "Original Sheet Count: " & OrigSheetCount & vbCrLf & "Final Sheet Count: " & FinalSheetCount

End Sub

 

[bullionbuks]

Now it says "orignal sheet count 1, final sheet count 4".

 

[Joe4]

Absolutely bizarre!

I wonder what happens if you change the number of worksheets in a new workbook to 1. The two shouldn't be tied together, but maybe they are (do you have the most recent updates for Office/Excel 2007 applied? maybe there was a bug that was fixed in later patches)?

In any event, try changing that setting and see if that makes any difference. Here is a link on where you can find that: http://office.microsoft.com/en-us/excel-help/insert-or-delete-a-worksheet-HP001217034.aspx. Go to the section marked "Change the number of worksheets in a new workbook".

 

[bullionbuks]

Already tried that method. Now are start with one sheet, but still adding three sheets at a time. Also I work in a large office and everyone has this problem.

 

[shg]

Do you have a workbook named Sheet.xltx in your XLSTART folder?

 

[Joe4]

When was the last time you did an update?

If you go to Excel Option and go to the Resources tab, on the last line ("about Microsoft Office Excel 2007"), what version are you on?

You may want to run the "Check for Updates" feature at the top of the Resources tab to make sure it just isn't some bug that was fixed in later features.

 

[bullionbuks]

Thanks for your help. I am going to try Microsoft.

 

[Joe4]

Also take a look at shg's comments.

It looks like a template could be causing the issues.