Add Page Macro

John

New Member
Joined
Mar 12, 2002
Messages
24
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

vconfused

Well-known Member
Joined
Jun 11, 2004
Messages
547
Something like this?

Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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.
Code:
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

Well-known Member
Joined
Jun 11, 2004
Messages
547
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,456
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. :oops:
Your code looks like it'll work fine.
(Nice little routine you wrote there.)

Dan
 

Forum statistics

Threads
1,147,846
Messages
5,743,518
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top