PDA

View Full Version : Add page if not enough



Djblois
07-13-2006, 01:13 PM
I am using this code to add a worksheet if it needs to:

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

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
07-13-2006, 02:21 PM
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:
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

HTH,

Djblois
07-14-2006, 06:06 AM
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
07-14-2006, 06:32 AM
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



Set Cust = WB.Worksheets.Add(After:=Wb.Worksheets(WB.Worksheets.Count))

Djblois
07-14-2006, 07:18 AM
No I only want to add a sheet if there are not enough sheets.

compariniaa
07-14-2006, 07:24 AM
what's "enough"?

Djblois
07-14-2006, 07:31 AM
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
07-14-2006, 07:47 AM
good point. sorry, i had a bit of a mental lapse (due to laziness to fully read any of your code)

Ken Puls
07-14-2006, 08:28 AM
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
07-14-2006, 09:47 AM
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
07-17-2006, 06:35 AM
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
07-17-2006, 06:49 AM
Daniel, please be more specific......what code doesn't work and doesn't switch to what.

Djblois
07-17-2006, 09:09 AM
Thank you lucas I think it is working now

Ken Puls
07-18-2006, 10:14 AM
What a thicko you are Ken!

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

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