Well not a lot of help from the forum on this one!
Anyhow, in the event someone else is looking to do this in the future I'll post what I ended up with:
To begin I learned that I needed to use the Workbook_NewSheet event to begin my process.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call AddButton
End Sub
As you can see I then added a macro that checked to see if the sheet we were on was NOT the Pivot Table and then if it wasn't proceeded to add our object:
Sub AddButton()
If ActiveSheet.Name <> "Traffic Log" Then
Call AddButtonAndCode
End If
End Sub
If we're not on the Pivot Table then we proceed with our next macro which creates the object (button) and the code that lives behind it:
Sub AddButtonAndCode()
Application.ScreenUpdating = False
ActiveSheet.Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.RowHeight = 31.5
Range("A2").Select
' Declare variables
Dim i As Long, Hght As Long
Dim Name As String, NName As String
' Set the button properties
i = 0
Hght = 305.25
' Set the name for the button
NName = "cmdAction" & i
' Test if there is a button already and if so, increment its name
For Each OLEObject In ActiveSheet.OLEObjects
If Left(OLEObject.Name, 9) = "cmdAction" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 9)
If Name >= i Then
i = Name + 1
End If
NName = "cmdAction" & i
Hght = Hght + 27
End If
Next
' Add button
Dim myCmdObj As OLEObject, N%
Set myCmdObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=3, Top:=3, Width:=130, Height:= _
26)
' Define buttons name
myCmdObj.Name = NName
' Define buttons caption
myCmdObj.Object.Caption = "RETURN TO REPORT"
' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "Call ReturnToPivot" 'here you see I'm calling my final macro
.InsertLines N + 4, vbNewLine
.InsertLines N + 5, "End Sub"
End With
Application.ScreenUpdating = True
End Sub
Then to finish I need to have a macro built with the code that I want to run using my newly created button:
Sub ReturnToPivot()
Application.DisplayAlerts = False
If InStr(ActiveSheet.Name, "Sheet") Then
ActiveSheet.Delete
Else: ActiveSheet.DrawingObjects.Delete
End If
Application.DisplayAlerts = True
End Sub
In this case I was looking to return to the Pivot Table and delete the newly created worksheet. Anyhow, hopefully someone else finds this useful as it took me the better part of the morning work my way through.