?B?SkxhdGhhbQ==?= <HelpFrom @ Jlathamsite.com.(removethis) wrote
in :

Not exactly what you want, but less susceptible to error:

Sub MakeBankSheets()
Dim myCount As Integer

If Worksheets("Master").Range("A15") 0 Then
For myCount = 1 To _
Worksheets("Master").Range("A15")
Worksheets("Bank").Copy after:=Worksheets(Worksheets.Count)
Next
End If
End Sub

It will make the number of copies of 'Bank' indicated, however it will
number the sheets starting with (2) and continuing upward, as 'Bank
(2)', 'Bank (3)' etc.

But this will do what you want, but you would run into troubles the
second time you ran it in the same workbook because you'd be trying to
name the new sheets with the name(s) of sheets already in the
workbook:

Sub MakeBankSheets()
Dim myCount As Integer

If Worksheets("Master").Range("A15") 0 Then
For myCount = 1 To _
Worksheets("Master").Range("A15")
Worksheets("Bank").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Bank" & Trim(Str(myCount))
Next
End If
End Sub


"OdAwG" wrote:

Hello All,

In my sheet named MASTER, I have a value stored in cell A15, a
numeric value of 10.
Depending on this value, i would like to make a copy of another
sheet called Bank, 10 times
and name each sheet Bank1, Bank2, Bank3, etc... Bank10.

This value, in cell A15, in sheet Master, can change manually
periodically (daily, weekly, monthly, and etc...)

is this possible to do with a macro?

Any and all help in this matter is greatly appreciated.

Argus







Just a slight tweak to this seems to get over the problem of trying to
create sheets with the same name likie this;

Sub MakeBankSheets()
Dim myCount As Integer

If Worksheets("Master").Range("A15") 0 Then
For myCount = 1 To _
Worksheets("Master").Range("A15")
Worksheets("Bank").Copy after:=Worksheets(Worksheets.Count)
' ActiveSheet.Name = "Bank" & Trim(Str(myCount))
ActiveSheet.Name = "Bank" & Trim((Worksheets.Count) - 2)
Next
End If
End Sub


Does anyone have any idea how you would get sheets created in this way
with 'months' as names - so you would end with a series of sheets called
- 'Jan', 'Feb', 'Mar' etc.

Any help appreciated