Add Page Macro

[John]

I need to be able to add pages to a workbook using Vb code. Each new page needs to go to the end of the workbook and be active.
I have used the following:
Sheets("TMU108 M").Visible = True
Sheets("TMU108 M").Select
Sheets("TMU108 M").Copy Before:=Sheets(14)
Sheets("TMU108 M (2)").Select
Sheets("TMU108 M (2)").Name = "TMU108 (2)"
Sheets("TMU108 M").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("TMU108 (2)").Select
This is fine for the first page but the next page is added in front of the first page. But to get the next page added to go to the end I have to change line 3 to Copy before:=Sheets(15). Since I need to add up to 30 pages there must be an easier way than re[peating this peiece of code 30 times.
Can anyone help please.

John

 

[vconfused]

Something like this?

Sub macro1()
Dim iCount As Integer, iPosition As Integer, iTotal As Integer
Dim shtSource As Worksheet

iPosition = 14
iTotal = 30

    Application.ScreenUpdating = False
    Set shtSource = Sheets("TMU108 M")
    With shtSource
        .Visible = True
        For iCount = 2 To iTotal
            .Copy Before:=Sheets(iPosition)
            ActiveSheet.Name = "TMU108 (" & iCount & ")"
            iCount = iCount + 1
            ActiveSheet.Visible = False
        Next
        .Visible = False
    End With
    Application.ScreenUpdating = True
    
End Sub

[/quote]

 

[HalfAce]

I need to be able to add pages to a workbook using Vb code. Each new page needs to go to the end of the workbook and be active. This should do just that.

Sub CopySheetToEnd()
Dim i As Integer
i = ThisWorkbook.Worksheets.Count
  With Sheets("TMU108 M")
    .Visible = True
    .Copy After:=Sheets(i)
    .Visible = False
  End With
End Sub

Hope it helps,
Dan

 

[vconfused]

But if you look at his sample, he is not really putting the new page at the end as he is pasting the new sheets "Before" another sheet. Also he is renaming the sheets.

 

[HalfAce]

I'lll be damned, you're right. I didn't read the botton part of the post well enough & just assumed we were looking at a recorded macro, not realizing the sheets were all getting added at once.
Your code looks like it'll work fine.
(Nice little routine you wrote there.)

Dan