I am using MS 2003 and these are password protected sheets. I have a macro
that when a user clicks this particular button it creates 4 extra duplicates
(Extra Earned Income Methd 1) of the same spread sheet and inserts the grand
total from the extra worksheet on to a master grand total worksheet (family
totals) at the end of the workbook.
What I want is that when the user clicks the command button that just one
extra duplicate is created, and the button could be clicked up to four times.
That way if they are not going to use all of them there are not so many open
tabs. Any ideas?? Below is my macro.

Thank you in advance for any help.

~Roxy

Private Sub CommandButton1_Click()
Worksheets("Extra Earned Income Methd 1").Visible = True
Sheets("Extra Earned Income Methd 1").Select

Worksheets("Extra Earned Income Methd 1").Visible = True
Worksheets("family totals").Range("A5").EntireRow.Hidden = False

Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Sheets("Extra Earned Income Methd 1 (2)").Select

Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Worksheets("family totals").Range("A6").EntireRow.Hidden = False

Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Sheets("Extra Earned Income Methd 1 (3)").Select

Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Worksheets("family totals").Range("A7").EntireRow.Hidden = False

Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Sheets("Extra Earned Income Methd 1 (4)").Select

Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Worksheets("family totals").Range("A8").EntireRow.Hidden = False
Sheets("Extra Earned Income Methd 1").Select

End Sub

Private Sub CommandButton1_Click()
Worksheets("Extra Earned Income Methd 1").Copy _
after:=Worksheets("Extra Earned Income Methd 1")
End Sub
"Roxy" wrote:

I am using MS 2003 and these are password protected sheets. I have a macro
that when a user clicks this particular button it creates 4 extra duplicates
(Extra Earned Income Methd 1) of the same spread sheet and inserts the grand
total from the extra worksheet on to a master grand total worksheet (family
totals) at the end of the workbook.
What I want is that when the user clicks the command button that just one
extra duplicate is created, and the button could be clicked up to four times.
That way if they are not going to use all of them there are not so many open
tabs. Any ideas?? Below is my macro.

Thank you in advance for any help.

~Roxy

Private Sub CommandButton1_Click()
Worksheets("Extra Earned Income Methd 1").Visible = True
Sheets("Extra Earned Income Methd 1").Select

Worksheets("Extra Earned Income Methd 1").Visible = True
Worksheets("family totals").Range("A5").EntireRow.Hidden = False

Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Sheets("Extra Earned Income Methd 1 (2)").Select

Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Worksheets("family totals").Range("A6").EntireRow.Hidden = False

Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Sheets("Extra Earned Income Methd 1 (3)").Select

Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Worksheets("family totals").Range("A7").EntireRow.Hidden = False

Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Sheets("Extra Earned Income Methd 1 (4)").Select

Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Worksheets("family totals").Range("A8").EntireRow.Hidden = False
Sheets("Extra Earned Income Methd 1").Select

End Sub

Is that all? I took out my entire code in inserted yours and now when I
click the button it just bumps me over to the next tab which is a completely
different worksheet...and no new one appears. Am I missing something?

Many thanks!
~Roxy

The code is copying everything on the "Extra Earned Income Methd 1"
worksheet. if you have a blank worksheet the the copied sheet will also be
blank. If no new sheets appear then I think you need to look at all you
worksheets by using the arrows at the bottom left corner of the window. You
probably have some sheets tabs that you are not seeing.

"Roxy" wrote:

Is that all? I took out my entire code in inserted yours and now when I
click the button it just bumps me over to the next tab which is a completely
different worksheet...and no new one appears. Am I missing something?

Many thanks!
~Roxy

Oops sorry I guess I wasn't clear enough, I have already created the 4 extra
sheets. They are hidden until the user clicks the button.
Is there a way to differentiate 'click number one' from 'click number two'?
Right now I have changed my macro so that when a user clicks the button just
once the "Extra Earned Income Meth 1" shows. But can the user go back click
the same button again, and get my already created sheet "Extra Earned Income
Meth 1 (2)" sheet to be visible, and so on for all the sheets?
This is what I've got so far, but it doesn't do anything if you try a second
click. I've renamed the Private Sub's even though there is only 1
CommandButton cause I kept getting an error...... Please let me know if I
don't make any sense macros/codes are still new to me :)

Private Sub CommandButton1_Click()
Worksheets("Extra Earned Income Methd 1").Visible = True
Sheets("Extra Earned Income Methd 1").Select

Worksheets("Extra Earned Income Methd 1").Visible = True
Worksheets("family totals").Range("A5").EntireRow.Hidden = False
End Sub
Private Sub CommandButton2_Click()
Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Sheets("Extra Earned Income Methd 1 (2)").Select

Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Worksheets("family totals").Range("A6").EntireRow.Hidden = False
End Sub
Private Sub CommandButton3_Click()
Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Sheets("Extra Earned Income Methd 1 (3)").Select

Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Worksheets("family totals").Range("A7").EntireRow.Hidden = False
End Sub
Private Sub CommandButton4_Click()
Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Sheets("Extra Earned Income Methd 1 (4)").Select

Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Worksheets("family totals").Range("A8").EntireRow.Hidden = False
Sheets("Extra Earned Income Methd 1").Select

End Sub

Does this code help???

Private Sub CommandButton1_Click()
SheetName = ActiveSheet.Name
'check if there is a parenthesis in sheet name
'No parenthesis then version 1
If InStr(SheetName, "(") = 0 Then
BaseName = SheetName
Version = 1
Else
'BaseName is sheet name left of parenthesis
'Use trim to remove space at end of name
BaseName = Trim(Left(SheetName, InStr(SheetName, "(") - 1))
'version is number after parenthis
Version = Mid(SheetName, InStr(SheetName, "(") + 1)
'remove closing parenthisis
Version = Val(Trim(Left(Version, _
InStr(Version, ")") - 1)))
End If

NextSheetName = BaseName & " (" & (Version + 1) & ")"
Worksheets(NextSheetName).Visible = True
Worksheets("family totals").Range("A5").EntireRow.Hidden = False
End Sub

"Roxy" wrote:

Oops sorry I guess I wasn't clear enough, I have already created the 4 extra
sheets. They are hidden until the user clicks the button.
Is there a way to differentiate 'click number one' from 'click number two'?
Right now I have changed my macro so that when a user clicks the button just
once the "Extra Earned Income Meth 1" shows. But can the user go back click
the same button again, and get my already created sheet "Extra Earned Income
Meth 1 (2)" sheet to be visible, and so on for all the sheets?
This is what I've got so far, but it doesn't do anything if you try a second
click. I've renamed the Private Sub's even though there is only 1
CommandButton cause I kept getting an error...... Please let me know if I
don't make any sense macros/codes are still new to me :)

Private Sub CommandButton1_Click()
Worksheets("Extra Earned Income Methd 1").Visible = True
Sheets("Extra Earned Income Methd 1").Select

Worksheets("Extra Earned Income Methd 1").Visible = True
Worksheets("family totals").Range("A5").EntireRow.Hidden = False
End Sub
Private Sub CommandButton2_Click()
Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Sheets("Extra Earned Income Methd 1 (2)").Select

Worksheets("Extra Earned Income Methd 1 (2)").Visible = True
Worksheets("family totals").Range("A6").EntireRow.Hidden = False
End Sub
Private Sub CommandButton3_Click()
Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Sheets("Extra Earned Income Methd 1 (3)").Select

Worksheets("Extra Earned Income Methd 1 (3)").Visible = True
Worksheets("family totals").Range("A7").EntireRow.Hidden = False
End Sub
Private Sub CommandButton4_Click()
Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Sheets("Extra Earned Income Methd 1 (4)").Select

Worksheets("Extra Earned Income Methd 1 (4)").Visible = True
Worksheets("family totals").Range("A8").EntireRow.Hidden = False
Sheets("Extra Earned Income Methd 1").Select

End Sub