Capturing open workbook as variable?

I've discovered that the code I have for pulling a template worksheet into an open workbook runs into problems that results in the code ThisWorkbook referencing the template, and not the open workbook.

   pvt_obj_Workbook.Worksheets("Ten").Copy Before:=ThisWorkbook.Worksheets(1)

I'm trying to capture the open workbook name into a variable to try and get my macro to work properly. The target (open workbook) will have multiple locations and names, but the template worksheet will be stored in a static location.

Public Sub copyNetContentsFiveWorksheet()
On Error Resume Next

'#
'# declare private variables
'#
   Dim pvt_obj_Workbook As Excel.Workbook
   Dim pvt_obj_TargetWorkbook As Excel.Workbook
   Dim pvt_str_WorksheetName As String

   Const pvt_cstr_TemplateWorkbook As String = "P:\QC Sheets\PRO-B 4.4 FM 03 04 Line 4&5 QC sheets.xlsm"
   
   pvt_obj_TargetWorkbook = ThisWorkbook
   
'#
'# prompt the user for the name to be given to the new worksheet after
'# the worksheet has been imported
'#
   pvt_str_WorksheetName = InputBox("Please enter date and shift for new sheet" & Chr(13) & _
                                    "eg.24-02-13-D or 24-02-13-N1." & Chr(13) & "DO NOT USE SLASHES ( \ or / )", "Add New Sheet")
   If Len(pvt_str_WorksheetName & "") = 0 Then
      Exit Sub
   End If
   
'#
'# freeze application user interface for performance reasons and suppress any warnings issued
'#
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   
'#
'# open the workbook holding the template worksheet to copy - note that the constant variable must hold the full
'# path and name of the workbook, for example C:\TEMP\TEMPLATE.XLSX
'#
   Set pvt_obj_Workbook = Workbooks.Open(pvt_cstr_TemplateWorkbook)
   
   If pvt_obj_Workbook Is Nothing Then
      MsgBox "Unable to open the template workbook at " & pvt_cstr_TemplateWorkbook, vbCritical, "Error message"
      GoTo RoutineExit
   End If
   
'#
'# copy the template worksheet and insert the worksheet into the current workbook before the
'# first existing worksheet
'#
   pvt_obj_Workbook.Worksheets("Five").Copy Before:=pvt_obj_TargetWorkbook.Worksheets(1)
   If Err.Number = 9 Then
      MsgBox "No worksheet named Template exists in workbook " & pvt_cstr_TemplateWorkbook, vbCritical, "Error message"
      GoTo RoutineExit
   ElseIf Err.Number > 0 Then
      MsgBox "Error " & Err.Number & " " & Err.Description & " occured while attempting to copy the template worksheet", _
         vbCritical, "Error message"
      GoTo RoutineExit
   End If
   
'#
'# attempt to rename the newly inserted worksheet - if the rename fails, delete the copied template
'# worksheet
'#
   pvt_obj_TargetWorkbook.Worksheets(1).Name = pvt_str_WorksheetName
   If Err.Number > 0 Then
      MsgBox "An error occured while trying to rename the imported worksheet to " & pvt_str_WorksheetName, vbCritical, "Error message"
      ThisWorkbook.Worksheets(1).Delete
      GoTo RoutineExit
   End If

RoutineExit:
   pvt_obj_Workbook.Close
   Application.ScreenUpdating = True
   Application.DisplayAlerts = True
   
End Sub

Any help would be greatly appreciated!

Okay, I think I'm following, more or less.

Scenario: You have two workbooks; one in which a user is entering data (Workbook1) and one which holds the data-entry template (Workbook2). When user clicks button on data-entry page in Workbook1, a template should be imported from Workbook2 into Workbook1. The newly-added template sheet added to Workbook1 needs a button which, when pressed, will fire off the same template-import macro as before, importing yet another copy of the template worksheet from Workbook2 into Workbook1.

Assuming the above scenario is correct, I would create the new buttons on the new sheets at the time they are being imported. This should simplify things since you'll only need to reference the template workbook long enough to copy the sheet over. Once the sheet is copied over, you can close the template workbook, add the new button, and assign it to the same macro you used originally.

I thought it would be clearer if I demonstrated with a fresh and very simple example. The code below covers the whole scenario (see attached workbooks for working example).

Option Explicit

Sub test()

    ' Variable to reference workbook holding macro (Starting Workbook)
    Dim wbThis As Workbook
    Set wbThis = ThisWorkbook
    
    ' Variable to reference template workbook
    Dim wbWithTemplate As Workbook
    Set wbWithTemplate = Workbooks.Open("C:\Users\Sean\Desktop\Template_Workbook.xlsm")
    
    ' Variable to reference template worksheet
    Dim wsTemplate As Worksheet
    Set wsTemplate = wbWithTemplate.Worksheets("Template_Worksheet")
    
    ' Copy template worksheet and paste into starting workbook
    wsTemplate.Copy Before:=wbThis.Worksheets(1)
    
    ' Turn off alerts so will close without prompting user
    Application.DisplayAlerts = False
    
    ' Close template workbook
    wbWithTemplate.Close
    
    ' Turn alerts back on
    Application.DisplayAlerts = True
    
    
    ' Variable to reference newly pasted sheet (in case you want to rename, etc.)
    Dim wsDestination As Worksheet
    Set wsDestination = ActiveSheet
    
   
    ' Add button
    ' Numbers in parentheses are location coordinates
    ActiveSheet.Buttons.Add(68.25, 56.25, 65.25, 32.25).Select
  
    ' Variable to reference button
    Dim btn As Button
    Set btn = ActiveSheet.Buttons(Selection.Name)
    
    ' Set button text to whatever you need
    btn.Caption = "Your Button Text"
    
    ' Deselect button
    wsDestination.Range("a1").Select
    
    
    ' Set macro to run when button is pressed
    btn.OnAction = "test"
    
    ' Clear objects
    Set btn = Nothing
    Set wsDestination = Nothing
    Set wsTemplate = Nothing
    Set wbWithTemplate = Nothing
    Set wbThis = Nothing
    
    
End Sub

Let me know if I've completely misunderstood or if you need more help plugging it into your situation.