VBA to copy existing sheet base on number entered in userform

[ziadh15]

Hello

I am new to VBA but did a lot of research and a lot of readings in order to create a VBA to my employer.
I have written the code but having some small bugs that you guys might be helpful.

Purpose of Code:
When you first open my excel sheet, only three pages will be visible( Sheet 1,2 & 11)
I have total of 11 pages; so the rest are hidden.
On Sheet11, I have a command button that will open up a userform.

In this userform, I have a list of objects where user will enter a number inside a textbox.
For each of those objects, I have an associated sheet that is hidden.
In each sheet, I have two pictures of that same object.
Purpose of this VBA, is for user to enter a quantity of each object and then the code will
1) show the associated sheet and
2) create copies of that sheet depending on quantity number entered by user.

Now the catch is that:
If user enters 1 or 2, I want VBA to show the original sheet.
If user enters 3 or 4, I want VBA to show the original sheet and make 1 copy of that sheet.
If user enters 5 or 6, I want VBA to shoe the original sheet and make 2 copies of that sheet.
etc
.
.
.

Since I am pretty new to VBA, I thought of taking it case by case which is stupid I know but couldn't find another way.
With my current code, case 1 works fine if user enters 1 or 2. But when user enters 3 or 4, it doesn't create a copy.

Below is my Code

Code
Private Sub CommandButton1_Click()

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Collars")

'Collar Sheet'
If TextBox1.Value = "1" Or "2" Then
wkb.Sheets("Collars").Visible = True
Else
wkb.Sheet11Visible = True
wkb.Sheet1.Visible = True
wkb.Sheet2.Visible = True


If TextBox1.Value = "3" Or "4" Then
wkb.Sheets("Collars").Visible = True
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
Else

wkb.Sheets("Collars").Visible = False
wkb.Sheets("Start").Visible = True
wkb.Sheets("Rev Log").Visible = True
wkb.Sheet2.Visible = True

End If
End If
End Sub

 

[L. Howard]

Try Select Case, for your 'cases'. Enter your Code-To-Do for the case as you want it to apply.

Howard

Private Sub CommandButton1_Click()

 Dim wkb As Workbook
 Set wkb = ThisWorkbook

 Dim ws1 As Worksheet
 Set ws1 = ThisWorkbook.Worksheets("Collars")

 'Collar Sheet'
 Select Case TextBox1.Value
 
   Case Is = 1 Or 2
    '
 
   Case Is = 3 Or 4
    '
  
   Case Else
    '

 End Select


 End Sub

 

[ziadh15]

Thanks for you reply.
Unfortunately it did not work, unless I wrote it wrong

Private Sub CommandButton1_Click()

Dim wkb As Workbook
Set wkb = ThisWorkbook

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Collars")

'Collar Sheet'
Select Case TextBox1.Value

Case Is = 1 Or 2
wkb.Sheets("Collars").Visible = True

Case Is = 3 Or 4
wkb.Sheets("Collars").Visible = True
ws1.Copy ThisWorkbook.Sheets(Sheets.Count)

End Select
End Sub



I think I am missing something ...

 

[L. Howard]

Hmm, I am thinking that the numbers from a text box are returned as text.
Try it like this.

Howard

Case Is = "1" Or "2"

 

[ziadh15]

I already tried that and it did not work.

With my original code, starting with case1, if user enters 1 or 2, I am able to make the corresponding sheet visible.
However, when enter 3 or 4, it also only makes the sheet visible but doesn't create a copy. what do you think?

Private Sub CommandButton1_Click()

 Dim wkb As Workbook
 Set wkb = ThisWorkbook

 Dim ws1 As Worksheet
 Set ws1 = ThisWorkbook.Worksheets("Collars")

 'Collar Sheet'
 If TextBox1.Value = "1" Or "2" Then
 wkb.Sheets("Collars").Visible = True
 Else
 wkb.Sheet11Visible = True
 wkb.Sheet1.Visible = True
 wkb.Sheet2.Visible = True


 If TextBox1.Value = "3" Or "4" Then
 wkb.Sheets("Collars").Visible = True
 ws1.Copy ThisWorkbook.Sheets(Sheets.Count)
 Else

 wkb.Sheets("Collars").Visible = False
 wkb.Sheets("Start").Visible = True
 wkb.Sheets("Rev Log").Visible = True
 wkb.Sheet2.Visible = True

 End If
 End If
 End Sub

 

[L. Howard]

Okay, I see.

I am sorry I don't have a definitive answer.

Howard

 

[ziadh15]

I finally got it to work.
Took the long-not smart- way but it works.

Private Sub CommandButton1_Click()

Dim wkb As Workbook
Dim x As Integer
Dim ws1 As Worksheet
Set wkb = ThisWorkbook


'''''''''''''''''''''''''''''''''''''''''''''COLLARS''''''''''''''''''''''''''''''''''''''''''''''
If TextBox1.Value = "1" Then
wkb.Sheets("Collars").Visible = True

ElseIf TextBox1.Value = "2" Then
wkb.Sheets("Collars").Visible = True

ElseIf TextBox1.Value = "3" Then
wkb.Sheets("Collars").Visible = True
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")

ElseIf TextBox1.Value = "4" Then
wkb.Sheets("Collars").Visible = True
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")

ElseIf TextBox1.Value = "5" Then
wkb.Sheets("Collars").Visible = True
For numtimes = 1 To 2
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
Next

ElseIf TextBox1.Value = "6" Then
wkb.Sheets("Collars").Visible = True
For numtimes = 1 To 2
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
Next

ElseIf TextBox1.Value = "7" Then
wkb.Sheets("Collars").Visible = True
For numtimes = 1 To 3
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
Next

ElseIf TextBox1.Value = "8" Then
wkb.Sheets("Collars").Visible = True
For numtimes = 1 To 3
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
Next

ElseIf TextBox1.Value = "9" Then
wkb.Sheets("Collars").Visible = True
For numtimes = 1 To 4
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
Next

ElseIf TextBox1.Value = "10" Then
wkb.Sheets("Collars").Visible = True
For numtimes = 1 To 4
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
Next

Else
MsgBox "Please enter a number between 1 and 10"

End If
'''''''''''''''''''''''''''''''''''''''''''''''''Add UnloadMe''''''''''''''''''''''''''''''''''''''''''''''''

''''''''''''''''''''''''''''''''''''''''''SPIRAL STABILIZERS'''''''''''''''''''''''''''''''''''''''''''''''''
End Sub

 

[ziadh15]

One more issue

My code below basically creates a copy of a sheet depending on number entered in textbox.
If user entered 8 for example, it will create 7 copies.

Issue:
I want them to be created in order as in: Collars , Collars (1), Collars (2), Collars (3) ...

My code below works perfectly fine but want to use a smarter code .. any help?

ElseIf TextBox1.Value = "8" Then
wkb.Sheets("Collars").Visible = True
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars")
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars (2)")
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars (3)")
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars (4)")
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars (5)")
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars (6)")
ActiveWorkbook.Sheets("Collars").Copy _
after:=ActiveWorkbook.Sheets("Collars (7)")