Add page if not enough

[Djblois]

I am using this code to add a worksheet if it needs to:

[VBA]If WB.Sheets.Count = 1 Then
Set Cust = WB.Worksheets.Add
Else: Set Cust = WB.Sheets(2)[/VBA]

However this works if it needs to add the sheet but not if it doesn't need to add the sheet? Please help.

[Ken Puls]

If I get you correctly, your issue is that your new sheet is added before the exisitng sheet. Setting to Worksheets(2) then activates the second sheet in the workbook, which was originally your first sheet.

Try this:
[vba]Sub test()
Dim wb As Workbook
Dim cust As Worksheet

Set wb = ActiveWorkbook
If wb.Sheets.Count = 1 Then
Set cust = wb.Worksheets.Add(, wb.Sheets(1))
Else
Set cust = wb.Worksheets(2)
cust.Activate
End If
End Sub[/vba]

HTH,

[Djblois]

That code is also only working if there is only one tab already but if there are 2 tabs already it doesn't work. I don't care where it adds it, I only care that it does add it and then switches to the new tab.

[Bob Phillips]

You are not clear. It works perfectly for me.

DO you mean you want to add a new sheet regardless, after the last sheet? If so, use

[vba]

Set Cust = WB.Worksheets.Add(After:=Wb.Worksheets(WB.Worksheets.Count))
[/vba]

[Djblois]

No I only want to add a sheet if there are not enough sheets.

[compariniaa]

what's "enough"?

[Djblois]

It shows im my code at the top if there are less than 2 then I want it to add a sheet. THe code is working if it needs to add it but it isn't working if it doesn't need to add a sheet.

[compariniaa]

good point. sorry, i had a bit of a mental lapse (due to laziness to fully read any of your code)

[Ken Puls]

It shows im my code at the top if there are less than 2 then I want it to add a sheet. THe code is working if it needs to add it but it isn't working if it doesn't need to add a sheet.

I'm sorry. Call me dense, if you like, but I really don't understand what you're asking.

The code I provided will only add a sheet to the workbook if there is 1 worksheet (less than 2) in the book. If there are more sheets, then it sets the 2nd worksheet to a variable and activates that sheet. To test it, add a second sheet, select the first sheet in the workbook, then run the code. It will select sheet 2.

Exactly what are you/are you not expecting to happen if there is more than 1 sheet? My guess is that it is just not doing what you really want it to do... As both Bob and I have pointed out, the code is doing what I told it to...

Can you explain a little more?

[Bob Phillips]

I'm sorry. Call me dense, if you like, but I really don't understand what you're asking.

The code I provided will only add a sheet to the workbook if there is 1 worksheet (less than 2) in the book. If there are more sheets, then it sets the 2nd worksheet to a variable and activates that sheet. To test it, add a second sheet, select the first sheet in the workbook, then run the code. It will select sheet 2.

Exactly what are you/are you not expecting to happen if there is more than 1 sheet? My guess is that it is just not doing what you really want it to do... As both Bob and I have pointed out, the code is doing what I told it to...

Can you explain a little more?

What a thicko you are Ken!

Oops, so am I because I don't understand either.

[Djblois]

For some reason the code works if it needs to add a sheet. However, if it doesn't need to add a sheet, it doesn't switch to it.

Daniel

[lucas]

Daniel, please be more specific......what code doesn't work and doesn't switch to what.

[Djblois]

Thank you lucas I think it is working now

[Ken Puls]

What a thicko you are Ken!

Oops, so am I because I don't understand either.

Hmmm... seems solved and I still feel thick!