Daxton's Question: New Worksheet Button on Every Worksheet

[Anne Troy]

Sub newWorksheet() 
' newWorksheet Macro
     ' Macro recorded 6/9/2004 by VMM
Dim latestSheet As Integer 
ActiveWindow.ScrollWorkbookTabs Position:=xlLast 
    Sheets(Worksheets.Count).Select 
    Sheets.Add 
    Sheets(Worksheets.Count).Select 
Sheets("Sheet6").Move After:=Sheets(14)
    Sheets(Worksheets.Count).Select 
    ActiveSheet.Shapes("Button 1").Select 
    Selection.Copy 
    Range("A1").Select 
    Sheets(Worksheets.Count - 1).Select 
    Range("A1").Select 
    ActiveSheet.Paste 
    Selection.ShapeRange.IncrementLeft 3.75 
    Range("A1").Select 
    latestSheet = Worksheets.Count - 1 
    Sheets(latestSheet).Move After:=Sheets(Sheets.Count) 
End Sub

[Daxton A.]

Does anyone know how to get this code to work. Its not that important to me but I just want to know. Cuz what I want to do is Have a button on ever worksheet that I create in this one workbook that creates a new sheet everytime it is pressed and from any page. But the code right there only works if it is pressed on the last sheet.

And Thank You again Dreamboat!

[Zack Barresse]

Hi Daxton,

If you want to just add a new sheet from anywhere in the workbook, and place that new sheet at the end of all the others, this'll do ya:

Sub addSheet()
    Sheets.Add
    ActiveSheet.Move after:=Sheets(Worksheets.Count)
End Sub

Put it in a regular module. Assign a shortcut key if you'd like, or assign to buttons on every sheet. My preference would be a shortcut key. Are you going to want to prompt for a new name also? That'd be easy enough to add too.

[NateO]

Hello try the following:

Private Sub Ad_SheetNButton()
 Dim ws As Worksheet, cb As OLEObject
 Application.ScreenUpdating = False
 With ThisWorkbook
    .Worksheets.Add , .Sheets(.Sheets.Count)
    Set ws = .Sheets(.Sheets.Count)
 End With
 With ws
    Set cb = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
    Left:=.Cells(1, 1).Left, _
    Top:=.Cells(1, 1).Top, _
    Width:=.Cells(1, 1).Width * 2, _
    Height:=Cells(1, 1).RowHeight * 2)
    With cb
        .Placement = xlMove
        With .Object
            .BackColor = &HFF&
            .Caption = "Press Me"
        End With
    End With
    ThisWorkbook.VBProject.VBComponents(.CodeName).CodeModule. _
     insertlines 1, _
     "Private Sub CommandButton1_Click()" & vbLf & _
     "Application.Run ""Ad_SheetNButton""" & vbLf & _
     "End Sub"
 End With
 Application.ScreenUpdating = True
 End Sub

[Steiner]

Hi Nate,


your code is quite cool:cool , but how about modifiying the line that inserts the code, because your version has a problem with option explicit turned on: your code is inserted before it, and I always get an error pressing the new button. But with the following modification the code will be added to the end of the code that is already there:

ThisWorkbook.VBProject.VBComponents(.CodeName).CodeModule. _
insertlines ThisWorkbook.VBProject.VBComponents(ThisWorkbook.CodeName).CodeModule.CountOfLines + 1, _
"Private Sub CommandButton1_Click()" & vbLf & _
"Application.Run ""Ad_SheetNButton""" & vbLf & _
"End Sub"

[Daxton A.]

Just want to say thank you 2 everyone for good responses.

[NateO]

Works for me Steiner. While I declare all variables, I dont use option explicit; the thought had not occurred to me.