How to add sheet

[bhandari]

if A1=1 then add one sheet
and change the Sheet name Pkg 1
if A1=2 then add 2 sheet
and change the name "1st Sheet"Pkg 1,"2nd Sheet " Name:Pkg 2

 

[My Aswer Is This]

Try this:

Assuming you want to make copies of the active sheet.

Sub My_Script()
'Modified 11-15-17 11:50 AM EST
Application.ScreenUpdating = False
Dim ans As String
Dim num As Long
ans = ActiveSheet.Name
num = Sheets(ans).Range("A1").Value
    For i = 1 To num
        Sheets(ans).Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Pkg" & i
    Next
Application.ScreenUpdating = True
End Sub

 

[bhandari]

Working fine Thank you..add Msg box "sheet already existed"
if i click your macro 2 times one more copy is coming can u fix this

 

[My Aswer Is This]

Not sure who your talking to. There have been several peole responding to this post.

Working fine Thank you..add Msg box "sheet already existed"
if i click your macro 2 times one more copy is coming can u fix this

 

[bhandari]

iam using @My Aswer Is This code its working fine ..i just want to fix it..if i click command button 2 times ome more sheet is popping with same sheet name

instead of this
i want to add msg box"sheet already existed"

 

[My Aswer Is This]

Try this:

Sub My_Script()
'Modified 11-15-17 12:17 AM EST
On Error GoTo M
Application.ScreenUpdating = False
Dim ans As String
Dim num As Long
ans = ActiveSheet.Name
num = Sheets(ans).Range("A1").Value
    For i = 1 To num
        Sheets(ans).Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Pkg" & i
    Next
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "That sheet allready exist"
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

 

[bhandari]

i have "Template sheet"

in this sheet A1=1 then generate only 1 sheet with Sheet tab name "Pkg 1"
if A2=2 then generate only 2 sheet with Sheet tab name "Pkg 1" and "Pkg 2"


@My Aswer Is This if i run your code sheet name is coming like that(i run your code 2 times)
1)Pkg1 (2) is showing(i dont want to see this)-->macro need to stop. it should not create copy of Pkg1 (Existed)
2)A1 value should not copy in all sheets make it as "blank cell" in new generating sheets..

Thank for your valuable replys..iam struck here from long time

 

[My Aswer Is This]

So you do not want to see value in Range("A1") on all sheets. That's simple.

But your not going to be able to run the script a second time.
If you run script second time script looks in Range(A1) to see how many times.
Script names sheets "Pk" and 1 and then 2 and the 3

Next time same thing "PK" and 1 and 2 and 3
So you get duplicates.


You do understand this is called "Mission Creep" a term meaning first you ask for "Milk" and I give you "Milk"

Then you say I want "Milk" and "Cheese" and I give you "Milk and Cheese"
Then you say I want "Milk" and "Cheese" and "Bread" and I give you "Milk Cheese and Bread"

And this can go on for ever.

In your original post you never said you wanted to do this more then once.

So now I guess what your wanting is:

If the first time I put 5 in (A1) you want to copy the master sheet 5 times
And name them PK 1 to 5

Next time you put 10 in Range(A1) on the master sheet
You want to copy the master sheet 10 times and name the sheets

PK and 6 and then 7 then 8 then 9 and on and on

Is that what you want?

 

[bhandari]

Thanks for nice example i have understood

From master sheet A1 value is copy in all sheets,cant we hide this value in new sheets?
or
i have a option to delete individually by going to every sheet?

 

[My Aswer Is This]

Even doing this will cause duplicates if you run the script a second time
i have "Template sheet"

in this sheet A1=1 then generate only 1 sheet with Sheet tab name "Pkg 1"
if A2=2 then generate only 2 sheet with Sheet tab name "Pkg 1" and "Pkg 2"

Why would you need to use A1 and A2

 

[My Aswer Is This]

Yes I said that was easy. Doing the other part is hard.

Thanks for nice example i have understood

From master sheet A1 value is copy in all sheets,cant we hide this value in new sheets?
or
i have a option to delete individually by going to every sheet?