Code to add wroksheets to a workbook

[CrankyLemming]

Hi

I have a 2 sheet workbook, and 2 questions about it.

1) If I input a (whole) number into cell C3 on Sheet1 ("Main"), I
want Excel to create that number of copies of sheet 2. EG, input 2,
and I end up with one "Main" sheet and identical sheets which are the
original sheet 2 and 2 copies.

I've played around some code, but just keep getting errors.

2) The other question is that I have the following code in the 'This
Workbook' module:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
ActiveSheet.Name = Range("AD24")
End Sub

.... which changes the name of the worksheet to match the contents of
cell AD24.

That particular cell contains the following formula:

=IF(D2="","Empty",IF(ISERR(LEFT(D2,FIND(" ",D2)-1)),D2,LEFT(D2,FIND("
",D2)-1)))

Cell D2 is for the input of a name. The formula above takes the
forename "Andrew Berry" and renames the sheet "Andrew." If there is no
input in D2, the worksheet name defaults to "Empty."

So, is there a way I can get the part that says "Empty" to become:
"Empty","Empty2","Empty3", etc, for as many pages as produced
required?

Thanks in advance

Steve

 

[Dave Peterson]

#1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim iCtr As Long

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("c3")) Is Nothing Then Exit Sub

With Me.Parent 'the workbook
If IsNumeric(Target.Value) Then
If Target.Value > 0 Then
For iCtr = 1 To Target.Value
.Worksheets("sheet2").Copy _
after:=.Worksheets(.Worksheets.Count)
Next iCtr
End If
End If
End With

End Sub

#2. I don't know how to get it in the formula, but maybe you could populate
that cell when you're adding the worksheets? But then you'd lose the formula.

 

[CrankyLemming]

#1.

Option Explicit

End With

Perfect; thank you. If I can't solve part 2, then I'll shift the sheet
naming to a button click.

Is there any way for your code to be amended to fill the A1 cell of
each new sheet with the iCtr number?

S

 

[Dave Peterson]

When you copy that sheet, it becomes the activesheet. So:

For iCtr = 1 To Target.Value
.Worksheets("sheet2").Copy _
after:=.Worksheets(.Worksheets.Count)
ActiveSheet.Range("a1").Value = iCtr
Next iCtr

worked ok for me.

 

[CrankyLemming]

When you copy that sheet, it becomes the activesheet. So:

For iCtr = 1 To Target.Value
.Worksheets("sheet2").Copy _
after:=.Worksheets(.Worksheets.Count)
ActiveSheet.Range("a1").Value = iCtr
Next iCtr

worked ok for me.

Does for me, too. What can I say? Brilliant. Thanks a lot.

S