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,

 

[Joe4]

Welcome to the Board!

Can you describe how you are inserting the new worksheets?
You are selecting multiple tabs before inserting, are you?
Also, which version of Excel are you using?

 

[bullionbuks]

I am using excel 2007 and using the shift F11 shortcut. I have tried using the right click method and I am getting the same results. I am not selecting multiple tabs only one at a time. If any other details could be useful let me know.

Thx

 

[Joe4]

How many sheets are there before you insert new ones (1 or 3)?

 

[bullionbuks]

There are 3. I have tried to delete all, but 1 then insert the sheet, yet I am getting the same results.

 

[Joe4]

So, you are saying if you start with 3, and try to insert one, you get 6.
And you start with 1, and try to insert one, you get 4?

Try this. Turn on your macro recorder, select any one sheet, and then try inserting one sheet. Then stop the macro recorder, and paste the code you created with the recorder back here. I am interested to see what Excel is doing to cause this.

I have Excel 2007 and looked for any setting that would cause this, and cannot find one. There is a setting for how many sheets you want new workbooks to have, but I cannot find anything that would seem to affect number of sheets inserted.

 

[bullionbuks]

Sub Insert_Sheet_Ex()
'
' Insert_Sheet_Ex Macro
'

'
Sheets.Add After:=Sheets(Sheets.Count)
End Sub

 

[Joe4]

Can you do me just one more favor?
After you start the macro, but before you insert a sheet, can you move around between sheets? I just want to make sure it is not holding some multiple sheet selection.

 

[bullionbuks]

Sub Insert_Sheet_Ex()
'
' Insert_Sheet_Ex Macro
'

'
ActiveSheet.Next.Select
ActiveSheet.Next.Select
ActiveSheet.Previous.Select
ActiveSheet.Previous.Select
ActiveSheet.Next.Select
ActiveSheet.Next.Select
Sheets.Add Type:="Worksheet"
End Sub

 

[Joe4]

Hmmm, that adds exactly one sheet for me.

I took your code and added some counters to it. Can you run this code, and tell me what the message box returns?

Sub Insert_Sheet_Ex()
'
' Insert_Sheet_Ex Macro
'
    Dim OrigSheetCount As Integer
    Dim FinalSheetCount As Integer
 
    OrigSheetCount = Sheets.Count
 
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Previous.Select
    ActiveSheet.Next.Select
    ActiveSheet.Next.Select
    Sheets.Add Type:="Worksheet"
 
    FinalSheetCount = Sheets.Count
 
    MsgBox "Original Sheet Count: " & OrigSheetCount & vbCrLf & "Final Sheet Count: " & FinalSheetCount
 
End Sub