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(quot;How many sheets do you want to copy?quot;, _
quot;Number of sheets to insertquot;)
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
If nwks = 255 Then
MsgBox quot;You cannot have more than 255 worksheets!quot;
Exit Sub
End If
Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
nwks = nwks 1
Sheets(nwks).Name = quot;Tabquot; amp; nwks
Next
Application.ScreenUpdating = True
End Sub
Regards
Peter
quot;WBTKbeezyquot; wrote:
gt; Help!
gt;
gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt;
gt; So I need a macro that would easily allow me to Add A user defined number of
gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; specifies. CAN ANYONE Help?
Why can't you have more than 255 sheets? It's not an excel restriction.
PeterAtherton wrote:
gt;
gt; This copies sheet 1 t the back of the workbook
gt;
gt; Sub NewSheets()
gt; Dim nwks As Integer, newSheet As Worksheet
gt; Dim nSheets As Integer, i As Integer
gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; quot;Number of sheets to insertquot;)
gt; Application.ScreenUpdating = False
gt; For i = 1 To nSheets
gt; nwks = Worksheets.Count
gt; If nwks = 255 Then
gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; Exit Sub
gt; End If
gt;
gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; nwks = nwks 1
gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; Next
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt; Regards
gt; Peter
gt;
gt; quot;WBTKbeezyquot; wrote:
gt;
gt; gt; Help!
gt; gt;
gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt;
gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; specifies. CAN ANYONE Help?
--
Dave Peterson
Dave
I thought it was - is there any restriction?
Regards
Peter
quot;Dave Petersonquot; wrote:
gt; Why can't you have more than 255 sheets? It's not an excel restriction.
gt;
gt; PeterAtherton wrote:
gt; gt;
gt; gt; This copies sheet 1 t the back of the workbook
gt; gt;
gt; gt; Sub NewSheets()
gt; gt; Dim nwks As Integer, newSheet As Worksheet
gt; gt; Dim nSheets As Integer, i As Integer
gt; gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; gt; quot;Number of sheets to insertquot;)
gt; gt; Application.ScreenUpdating = False
gt; gt; For i = 1 To nSheets
gt; gt; nwks = Worksheets.Count
gt; gt; If nwks = 255 Then
gt; gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; gt; nwks = nwks 1
gt; gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; gt; Next
gt; gt; Application.ScreenUpdating = True
gt; gt; End Sub
gt; gt;
gt; gt; Regards
gt; gt; Peter
gt; gt;
gt; gt; quot;WBTKbeezyquot; wrote:
gt; gt;
gt; gt; gt; Help!
gt; gt; gt;
gt; gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt; gt;
gt; gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; gt; specifies. CAN ANYONE Help?
gt;
gt; --
gt;
gt; Dave Peterson
gt;
The total number of sheets is limited by your pc's memory.PeterAtherton wrote:
gt;
gt; Dave
gt;
gt; I thought it was - is there any restriction?
gt;
gt; Regards
gt; Peter
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; Why can't you have more than 255 sheets? It's not an excel restriction.
gt; gt;
gt; gt; PeterAtherton wrote:
gt; gt; gt;
gt; gt; gt; This copies sheet 1 t the back of the workbook
gt; gt; gt;
gt; gt; gt; Sub NewSheets()
gt; gt; gt; Dim nwks As Integer, newSheet As Worksheet
gt; gt; gt; Dim nSheets As Integer, i As Integer
gt; gt; gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; gt; gt; quot;Number of sheets to insertquot;)
gt; gt; gt; Application.ScreenUpdating = False
gt; gt; gt; For i = 1 To nSheets
gt; gt; gt; nwks = Worksheets.Count
gt; gt; gt; If nwks = 255 Then
gt; gt; gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; gt; gt; Exit Sub
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; gt; gt; nwks = nwks 1
gt; gt; gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; gt; gt; Next
gt; gt; gt; Application.ScreenUpdating = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Regards
gt; gt; gt; Peter
gt; gt; gt;
gt; gt; gt; quot;WBTKbeezyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Help!
gt; gt; gt; gt;
gt; gt; gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt; gt; gt;
gt; gt; gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; gt; gt; specifies. CAN ANYONE Help?
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;
--
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 quot;Sheet 1quot; to quot;Sheet 2quot; (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!quot;PeterAthertonquot; wrote:
gt; This copies sheet 1 t the back of the workbook
gt;
gt; Sub NewSheets()
gt; Dim nwks As Integer, newSheet As Worksheet
gt; Dim nSheets As Integer, i As Integer
gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; quot;Number of sheets to insertquot;)
gt; Application.ScreenUpdating = False
gt; For i = 1 To nSheets
gt; nwks = Worksheets.Count
gt; If nwks = 255 Then
gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; Exit Sub
gt; End If
gt;
gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; nwks = nwks 1
gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; Next
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt; Regards
gt; Peter
gt;
gt; quot;WBTKbeezyquot; wrote:
gt;
gt; gt; Help!
gt; gt;
gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt;
gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; specifies. CAN ANYONE Help?
Thanks Dave!
Regards
Peter
quot;Dave Petersonquot; wrote:
gt; The total number of sheets is limited by your pc's memory.
gt;
gt;
gt; PeterAtherton wrote:
gt; gt;
gt; gt; Dave
gt; gt;
gt; gt; I thought it was - is there any restriction?
gt; gt;
gt; gt; Regards
gt; gt; Peter
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; Why can't you have more than 255 sheets? It's not an excel restriction.
gt; gt; gt;
gt; gt; gt; PeterAtherton wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; This copies sheet 1 t the back of the workbook
gt; gt; gt; gt;
gt; gt; gt; gt; Sub NewSheets()
gt; gt; gt; gt; Dim nwks As Integer, newSheet As Worksheet
gt; gt; gt; gt; Dim nSheets As Integer, i As Integer
gt; gt; gt; gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; gt; gt; gt; quot;Number of sheets to insertquot;)
gt; gt; gt; gt; Application.ScreenUpdating = False
gt; gt; gt; gt; For i = 1 To nSheets
gt; gt; gt; gt; nwks = Worksheets.Count
gt; gt; gt; gt; If nwks = 255 Then
gt; gt; gt; gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; gt; gt; gt; Exit Sub
gt; gt; gt; gt; End If
gt; gt; gt; gt;
gt; gt; gt; gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; gt; gt; gt; nwks = nwks 1
gt; gt; gt; gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; gt; gt; gt; Next
gt; gt; gt; gt; Application.ScreenUpdating = True
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; Regards
gt; gt; gt; gt; Peter
gt; gt; gt; gt;
gt; gt; gt; gt; quot;WBTKbeezyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Help!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; gt; gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; gt; gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; gt; gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; gt; gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; gt; gt; gt; specifies. CAN ANYONE Help?
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson
gt;
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(quot;How many sheets do you want to copy?quot;, _
quot;Number of sheets to insertquot;, 1)
Application.ScreenUpdating = False
For i = 1 To nSheets
nwks = Worksheets.Count
Sheets(nwks).Select
ncols = Range(quot;A1quot;).CurrentRegion.Columns.Count
nrows = Range(quot;a1quot;).CurrentRegion.Rows.Count
Sheets(nwks).Copy After:=Sheets(nwks)
nwks = nwks 1
Sheets(nwks).Name = quot;Tabquot; amp; nwks
Cells(2, ncols - 1).Select
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Next
Application.ScreenUpdating = True
End Sub
Regards
Peter
quot;WBTKbeezyquot; wrote:
gt; That works, but now I have encountered something I didn't think about...
gt; maybe you can help me out...
gt;
gt; The sheet that it copies from is full of info, and it needs to be copied...
gt; the new sheet also needs to have anything that quot;Sheet 1quot; to quot;Sheet 2quot; (etc
gt; all through until the user defined amount of new sheets. Then references need
gt; to be updated on two separate summary sheets.
gt;
gt; One sheet just needs rows added, and the other ones need columns added
gt; (which I am not sure how to do since they are letters, can you tell the macro
gt; insert column X and Y, then shift that over by 2 each time?)
gt;
gt; Any help would be appreciated!
gt;
gt;
gt; quot;PeterAthertonquot; wrote:
gt;
gt; gt; This copies sheet 1 t the back of the workbook
gt; gt;
gt; gt; Sub NewSheets()
gt; gt; Dim nwks As Integer, newSheet As Worksheet
gt; gt; Dim nSheets As Integer, i As Integer
gt; gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; gt; quot;Number of sheets to insertquot;)
gt; gt; Application.ScreenUpdating = False
gt; gt; For i = 1 To nSheets
gt; gt; nwks = Worksheets.Count
gt; gt; If nwks = 255 Then
gt; gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; gt; nwks = nwks 1
gt; gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; gt; Next
gt; gt; Application.ScreenUpdating = True
gt; gt; End Sub
gt; gt;
gt; gt; Regards
gt; gt; Peter
gt; gt;
gt; gt; quot;WBTKbeezyquot; wrote:
gt; gt;
gt; gt; gt; Help!
gt; gt; gt;
gt; gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt; gt;
gt; gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; gt; specifies. CAN ANYONE Help?
Thanks Peter for all your help... you've been very helpful!
quot;PeterAthertonquot; wrote:
gt; Hi
gt;
gt; I'm not sure that I quite know what you require, but try this.
gt; It copies the last sheet and moves the last two columns to the right.
gt;
gt; Sub NewSheets()
gt; Dim nwks As Integer, ncols As Integer, nrows As Long
gt; Dim nSheets As Integer, i As Integer
gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; quot;Number of sheets to insertquot;, 1)
gt; Application.ScreenUpdating = False
gt; For i = 1 To nSheets
gt; nwks = Worksheets.Count
gt; Sheets(nwks).Select
gt; ncols = Range(quot;A1quot;).CurrentRegion.Columns.Count
gt; nrows = Range(quot;a1quot;).CurrentRegion.Rows.Count
gt; Sheets(nwks).Copy After:=Sheets(nwks)
gt; nwks = nwks 1
gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; Cells(2, ncols - 1).Select
gt; Selection.EntireColumn.Insert
gt; Selection.EntireColumn.Insert
gt; Next
gt; Application.ScreenUpdating = True
gt; End Sub
gt;
gt; Regards
gt; Peter
gt;
gt;
gt;
gt; quot;WBTKbeezyquot; wrote:
gt;
gt; gt; That works, but now I have encountered something I didn't think about...
gt; gt; maybe you can help me out...
gt; gt;
gt; gt; The sheet that it copies from is full of info, and it needs to be copied...
gt; gt; the new sheet also needs to have anything that quot;Sheet 1quot; to quot;Sheet 2quot; (etc
gt; gt; all through until the user defined amount of new sheets. Then references need
gt; gt; to be updated on two separate summary sheets.
gt; gt;
gt; gt; One sheet just needs rows added, and the other ones need columns added
gt; gt; (which I am not sure how to do since they are letters, can you tell the macro
gt; gt; insert column X and Y, then shift that over by 2 each time?)
gt; gt;
gt; gt; Any help would be appreciated!
gt; gt;
gt; gt;
gt; gt; quot;PeterAthertonquot; wrote:
gt; gt;
gt; gt; gt; This copies sheet 1 t the back of the workbook
gt; gt; gt;
gt; gt; gt; Sub NewSheets()
gt; gt; gt; Dim nwks As Integer, newSheet As Worksheet
gt; gt; gt; Dim nSheets As Integer, i As Integer
gt; gt; gt; nSheets = InputBox(quot;How many sheets do you want to copy?quot;, _
gt; gt; gt; quot;Number of sheets to insertquot;)
gt; gt; gt; Application.ScreenUpdating = False
gt; gt; gt; For i = 1 To nSheets
gt; gt; gt; nwks = Worksheets.Count
gt; gt; gt; If nwks = 255 Then
gt; gt; gt; MsgBox quot;You cannot have more than 255 worksheets!quot;
gt; gt; gt; Exit Sub
gt; gt; gt; End If
gt; gt; gt;
gt; gt; gt; Sheets(quot;Tab1quot;).Copy After:=Sheets(nwks)
gt; gt; gt; nwks = nwks 1
gt; gt; gt; Sheets(nwks).Name = quot;Tabquot; amp; nwks
gt; gt; gt; Next
gt; gt; gt; Application.ScreenUpdating = True
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; Regards
gt; gt; gt; Peter
gt; gt; gt;
gt; gt; gt; quot;WBTKbeezyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Help!
gt; gt; gt; gt;
gt; gt; gt; gt; I have a workbook with 13 tabs. Sometimes we have a need to up that to over
gt; gt; gt; gt; 40 more, but all the new tabs would just be a copy of the other ones.
gt; gt; gt; gt;
gt; gt; gt; gt; So I need a macro that would easily allow me to Add A user defined number of
gt; gt; gt; gt; additional tabs each being a copy of one of the originals. Each Tab would
gt; gt; gt; gt; also need to be named in succession (i.e., Tab 13, copies and pastes as Tab
gt; gt; gt; gt; 14, then Tab 15, then Tab 16, then Tab 17, etc... up to as many as the user
gt; gt; gt; gt; specifies. CAN ANYONE Help?
留言列表
