Excel - Renaming Worksheets

[dipitiduda2]

Hello,
Working in Excel XP and need assistance with VB coding listed below. Trying to copy the ActiveSheet and rename the sucessive sheets.

VB gives me a 'Run-time error' that says, "Cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook reference by Visual Basic".

Now, if I cannot rename a sheet - "Adjustment 1" copied to "Adjustment 1(2)" - to the same name as the previous sheet, what code do I use?

What I need is for the first copied sheet to be named "Adjustment 1", the second sheet to be named "Adjustment 2", the third to "Adjustment 3", etc.

Private Sub cmdAdjustment_Add_Btn_Click()

ActiveSheet.Unprotect
MsgBox ".......", vbYes, "Add an Adjustment"
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "Adjustment 1"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
MsgBox "......", vbExclamation, "Warning!"

End Sub

Thank you!

 

[Spellman]

Try creating a static variable like

static count = 0

then set the line

ActiveSheet.name = "Adjustment" & Count
Count = Count + 1

Hope this helps a little. Mark

The key to immortality is to make a big impression in this life!!

 

[dipitiduda2]

Thanks for your quick response, Mark. I'm still having trouble placing the "Static Count = 0" code in the procedure. Here's what I have now. What am I not seeing? I've searched Help feature....just cannot make the code work.

One note, once the Adj 1 is created, Adj 2 is copied from Adj 1, then Adj 3 is copied from Adj 2, and so on.

Code:

Private Sub cmdAdjustment_Add_Btn_Click()

Static intCount As Integer

ActiveSheet.Unprotect
MsgBox ".....", vbYes, "Add an Adjustment"
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "Adjustment" & " " & Count
Count = Count + 1
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
MsgBox "....", vbExclamation, "Warning!"

End Sub

Thanks!

 

[ADoozer]

hi dipitiduda: here is a link to an faq i am writing, there is a (small) bit of code that may help you with renaming the worksheet

faq222-3383

good luck If somethings hard to do, its not worth doing - Homer Simpson

 

[johnwm]

You appear to be declaring {b}intCount[/b] then using Count
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[dipitiduda2]

Thanks for the help....johnwm....this is the code that you helped me out with last week for copying from one worksheet to another....I almost have it.....ADoozer.....your faq was super....I forwarded it to our dBase Sup also....gracias