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]

Here this makes sure Range("A1") is empty on all new sheets

Sub My_Script()
'Modified 11-15-17 1:27 PM 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
        ActiveSheet.Range("A1").Value = ""
    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]

At last i got it Thank yo u save my time

 

[My Aswer Is This]

If you want to do this more then once and create more sheets the second time do this

If you put 5 in Range ("A1" of the master sheet you will get 5 copies of the master sheet
Second time if you put 10 you will get 10 new sheets with new names.

This is assuming your master sheet is sheet(1) meaning it's in the far left position on the taskbar.

and all our new sheets are going after the master sheet.

Try this:

Sub My_Script()
'Modified 11-15-17 1:40 PM 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" & ActiveSheet.Index - 1
        ActiveSheet.Range("A1").Value = ""
    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]

After producing new sheets
Hide master sheet
Xlsheetvisibe=true
I don't know how to use it..can u please add this to your macro

 

[My Aswer Is This]

Are you talking about the script in Post 23?

What is the name of this sheet I have been calling Master

And how do you plan to unhide the sheet.

And are going to try and run the script from a hidden sheet,
I have not tested to see if that would work.

 

[bhandari]

Sheet Name is "Master"
Only one time giving value in A1 in Master sheet,so no need to use after generating new sheets
Hide Master sheet after generating new sheets .
At the end of your code add master sheet hidden

From hidden sheet it may not work
It must be active to generate new sheets
After running macro we can hide this sheet

 

[My Aswer Is This]

Here:
You add it:

Sheets("Master").Visible = False