Daxton's Question: New Worksheet Button on Every Worksheet

Code:

---

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

---

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.:dunno

And Thank You again Dreamboat!

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:

Code:

---

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.

Hello try the following:

Code:

---

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

---

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:

Code:

---

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"

---

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

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