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?
|
Similar Threads
