Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

Why can't you have more than 255 sheets? It's not an excel restriction.

PeterAtherton wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

--

Dave Peterson

Dave

I thought it was - is there any restriction?

Regards
Peter

"Dave Peterson" wrote:

Why can't you have more than 255 sheets? It's not an excel restriction.

PeterAtherton wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

--

Dave Peterson

The total number of sheets is limited by your pc's memory.


PeterAtherton wrote:

Dave

I thought it was - is there any restriction?

Regards
Peter

"Dave Peterson" wrote:

Why can't you have more than 255 sheets? It's not an excel restriction.

PeterAtherton wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

--

Dave Peterson


--

Dave Peterson

That works, but now I have encountered something I didn't think about...
maybe you can help me out...

The sheet that it copies from is full of info, and it needs to be copied...
the new sheet also needs to have anything that "Sheet 1" to "Sheet 2" (etc
all through until the user defined amount of new sheets. Then references need
to be updated on two separate summary sheets.

One sheet just needs rows added, and the other ones need columns added
(which I am not sure how to do since they are letters, can you tell the macro
insert column X and Y, then shift that over by 2 each time?)

Any help would be appreciated!


"PeterAtherton" wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

Thanks Dave!

Regards
Peter

"Dave Peterson" wrote:

The total number of sheets is limited by your pc's memory.


PeterAtherton wrote:

Dave

I thought it was - is there any restriction?

Regards
Peter

"Dave Peterson" wrote:

Why can't you have more than 255 sheets? It's not an excel restriction.

PeterAtherton wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

--

Dave Peterson


--

Dave Peterson

Hi

I'm not sure that I quite know what you require, but try this.
It copies the last sheet and moves the last two columns to the right.

Sub NewSheets()
Dim nwks As Integer, ncols As Integer, nrows As Long
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert", 1)
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
Sheets(nwks).Select
ncols = Range("A1").CurrentRegion.Columns.Count
nrows = Range("a1").CurrentRegion.Rows.Count
Sheets(nwks).Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Cells(2, ncols - 1).Select
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter



"WBTKbeezy" wrote:

That works, but now I have encountered something I didn't think about...
maybe you can help me out...

The sheet that it copies from is full of info, and it needs to be copied...
the new sheet also needs to have anything that "Sheet 1" to "Sheet 2" (etc
all through until the user defined amount of new sheets. Then references need
to be updated on two separate summary sheets.

One sheet just needs rows added, and the other ones need columns added
(which I am not sure how to do since they are letters, can you tell the macro
insert column X and Y, then shift that over by 2 each time?)

Any help would be appreciated!


"PeterAtherton" wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?

Thanks Peter for all your help... you've been very helpful!

"PeterAtherton" wrote:

Hi

I'm not sure that I quite know what you require, but try this.
It copies the last sheet and moves the last two columns to the right.

Sub NewSheets()
Dim nwks As Integer, ncols As Integer, nrows As Long
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert", 1)
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
Sheets(nwks).Select
ncols = Range("A1").CurrentRegion.Columns.Count
nrows = Range("a1").CurrentRegion.Rows.Count
Sheets(nwks).Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Cells(2, ncols - 1).Select
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter



"WBTKbeezy" wrote:

That works, but now I have encountered something I didn't think about...
maybe you can help me out...

The sheet that it copies from is full of info, and it needs to be copied...
the new sheet also needs to have anything that "Sheet 1" to "Sheet 2" (etc
all through until the user defined amount of new sheets. Then references need
to be updated on two separate summary sheets.

One sheet just needs rows added, and the other ones need columns added
(which I am not sure how to do since they are letters, can you tell the macro
insert column X and Y, then shift that over by 2 each time?)

Any help would be appreciated!


"PeterAtherton" wrote:

This copies sheet 1 t the back of the workbook

Sub NewSheets()
Dim nwks As Integer, newSheet As Worksheet
Dim nSheets As Integer, i As Integer
nSheets = InputBox("How many sheets do you want to copy?", _
"Number of sheets to insert")
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox "You cannot have more than 255 worksheets!"
Exit Sub
End If

Sheets("Tab1").Copy After:=Sheets(nwks)
nwks = nwks + 1
Sheets(nwks).Name = "Tab" & nwks
Next
Application.ScreenUpdating = True
End Sub

Regards
Peter

"WBTKbeezy" wrote:

Help!

I have a workbook with 13 tabs. Sometimes we have a need to up that to over
40 more, but all the new tabs would just be a copy of the other ones.

So I need a macro that would easily allow me to Add A user defined number of
additional tabs each being a copy of one of the originals. Each Tab would
also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
specifies. CAN ANYONE Help?