Copying sheet multiple times, with buttons and codes

[Znicker]

Hi,
I am quite new to vba.

I have a workbook that is going to consist of several sheets namet «Teig001» all the way up to «teig200». The heading of each sheets(cell b2) should be the same as the sheet name.

All i got so far is a complete version of the sheet «teig001» and i so wish to make a vba to help me create the other 199 Sheets. With the same content(expept heading), buttons, and vba codes.

Is this possible?

 

[asjmoron]

Can you name your sheets simply "001", "002"....

if so,

Sub Create()


'
Application.DisplayAlerts = False
Sheets(Sheets.Count).Select

Dim I As Long
Dim xNumber As Integer
Dim xName As String
Dim xActiveSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Set xActiveSheet = ActiveSheet
xNumber = InputBox("How Many More sheets do you need?")
I = 1
Do Until I = xNumber + 1
xName = ActiveSheet.Name
xActiveSheet.Copy After:=ActiveWorkbook.Sheets(xName)
ActiveSheet.Name = Format(CLng(xActiveSheet.Name) + I, "000")
I = I + 1
Loop
xActiveSheet.Activate
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

 

[Znicker]

Thank you for quick response!

It kind of important that the names are Teig001, Teig002 and so on. They are part og code on a main sheet i have..

 

[Akuini]

Thank you for quick response!

It kind of important that the names are Teig001, Teig002 and so on. They are part og code on a main sheet i have..

Try this:

Sub a1079620a()
'https://www.mrexcel.com/forum/excel-questions/1079620-copying-sheet-multiple-times-buttons-codes.html
Dim i As Long, ws As Worksheet
Application.ScreenUpdating = False
Set ws = ActiveSheet
    For i = 2 To 200
        ws.Copy After:=Sheets(Worksheets.count)
        Sheets(Worksheets.count).Name = "Teig" & Format(i, "000")
        Range("B2") = ActiveSheet.Name
    Next
Application.ScreenUpdating = True
End Sub

 

[Znicker]

Hi,
And thank you for helpfull replies. All sorted now.

Is it possible to make a macro that opens another non-windows program an perform this action=

Open program
Press space+alt
down arrow four times
Enter
Paste
Enter
Back to same page in excel.

Is this possible?

Thanks
Magne

 

[Akuini]

Hi,
And thank you for helpfull replies. All sorted now.

Is it possible to make a macro that opens another non-windows program an perform this action=

Open program
Press space+alt
down arrow four times
Enter
Paste
Enter
Back to same page in excel.

Is this possible?

Thanks
Magne

You're welcome.
Since it is a new problem, you might want to start a new thread.

 

[Znicker]

Thats a good idea

Thanks
Magne