Macro to copy a sheet and name it

[OdAwG]

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

 

[Guest]

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

 

[Isissoft]

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

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

 

[Guest]

Good concept! - might have to play around with it some to get it to work out
properly, depending on number of sheets in the workbook at the start of it
each time. I believe the value '2' should be the number of sheets in the
workbook other than the Bank# sheets?

 

[OdAwG]

hey All,

thanks for the reply, the solution here works like a charm, but, now, I have
an issue? If the sheets already exists, how can I delete them first before
running this new macro.

I could add the following to the new macro first and do the following but, I
would not necessary now how many sheets there are to code for because the
range could vary.
Worksheets("Bank1").Delete
Worksheets("Bank2").Delete
Worksheets("Bank3").Delete
Worksheets("Bank4").Delete
and etc...

If I were to code for let's say 12, and I only have ten (10) I would get an
error after the 10th deletion; also how do I disable the popup warnign box..
Is it possible to read or count the number of exisiting sheet with the name
Bank?, read that into an array and then delete them?

Thanks for the help

Argus

 

[Guest]

This should do the trick. Will ALWAYS delete any existing Bank# sheets in
the workbook (without deleting the 'Bank' sheet itself from which the others
are created) and then copy Bank to new Bank# sheets as indicated in A15 on
Master.

However, if you have other sheets in the workbook that start with the word
"Bank" (or "bank" or "BANK" ... well you get the idea, such as 'Bank
Receipts') then those sheets will get deleted also. The test is: is sheet
name longer than 4 characters, and if it is, then are the 1st 4 characters
"bank" in any format? If those two tests pass, then the sheet is deleted.

If you have other sheets that may be affected, such as 'Bank Receipts' or
'Bank Summary', you could exclude them by name in the delete loop with code
like:

For Each anySheet In Worksheets
If Len(anySheet.Name) > 4 And _
UCase(Left(anySheet.Name, 4)) = "BANK" Then
If anySheet.Name <> "Bank Receipt" And _
anySheet.Name <> "Bank Summary" Then
anySheet.Delete
End If
End If
Next



Here's the code (seems a shame to do away with Isissoft's great idea, but...)

Sub MakeBankSheets()
Dim myCount As Integer
Dim anySheet As Worksheet

If Worksheets("Master").Range("A15") > 0 Then
'delete existing Bank# sheets
Application.DisplayAlerts = False
For Each anySheet In Worksheets
If Len(anySheet.Name) > 4 And _
UCase(Left(anySheet.Name, 4)) = "BANK" Then
anySheet.Delete
End If
Next
Application.DisplayAlerts = True
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]

Hey "J",

It's not deleting the sheets. It is only adding them.

so if I had sheet names Bank1, Bank2, and etc... to Bank10, it not deleting
them, it adds ten (10) more. so now I have twenty (20) sheets. Bank11,
Bank12, and etc.. to Bank20.

It appears to be ignoring the delete part of the code I guess.

 

[Guest]

Check the spelling of Bank on your basic 'Bank' sheet. It should NOT have
any white space as any part of the name it should be 'Bank', not " Bank" or
"Bank " - this is an error I see sometimes. It worked just great here for me
basing everything on a sheet named 'Bank'.

If you're using the 'basic' code and your sheet names appear to start with
the word, then I would strongly suspect that there's a space or other
whitespace in front of the word 'Bank' on your sheets. If that were the
case, then the first 4 characters of your sheet names would not be "bank",
they'd be something like " ban" or " ba" and so it would fail the test that
needs to be passed before the sheet is deleted.

The workbook can't be protected for content either - if you've got the
workbook protected, it won't allow sheets to be deleted.