Macro to copy existing sheet and linking cells of new sheet to master sheet

[Milo2019]

This is the Macro I have to create a new sheet from my template (labeled as COQ 001) which works:
Sub CreateSheet()
Dim xName As String
Dim xSht As Object
Dim xNWS As Worksheet
On Error Resume Next
xName = InputBox("Please enter COQ Numeber. For Example: COQ 00X", "NEW QOQ")
If xName = "COQ 001" Then Exit Sub
Set xSht = Sheets(xName)
If Not xSht Is Nothing Then
MsgBox "Sheet cannot be created as there is already a worksheet with the same name in this workbook"
Exit Sub
End If
Sheets("COQ 001").Copy after:=Sheets(Sheets.Count)
Set xNWS = Sheets(Sheets.Count)
xNWS.Name = xName
End Sub

This is the macro I have to link the template sheet (COQ 001) to the master sheet which also works:
Sub Link2Log()
'
' Link2Log Macro
'

'
Rows("10:10").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B10").Select
ActiveCell.FormulaR1C1 = "='COQ 002'!R[-3]C[5]"
Range("C10").Select
ActiveCell.FormulaR1C1 = "='COQ 002'!R[2]C:R[2]C[5]"
Range("D10").Select
ActiveCell.FormulaR1C1 = "='COQ 002'!R[-2]C[3]"
Range("E10").Select
ActiveCell.FormulaR1C1 = "='COQ 002'!R[-1]C[2]"
Range("G10").Select
ActiveCell.FormulaR1C1 = "='COQ 002'!R[40]C"
Range("G11").Select
End Sub

Question: How do I combine the two macros above into one, so that every time I create a new COQ sheet copied off the template, it will link the cells to the master sheet? I want it to add a row when I create a new sheet and each time link the cells to the master sheet. I don't know what to place in the above location to have it grab the newly created sheet and link it in leu of COQ 002 or the template sheet everytime , nor how to combine the two macros above. Please help, Thanks,

 

[Milo2019]

Any help would be appreciated!! Thanks,

 

[Milo2019]

I just need to combine these two macros into one button. I can attach the workbook if it would help.
Any help would be appreciated! Thanks!

 

[Milo2019]

I just need to combine these two macros into one button. I can attach the workbook if it would help.
Any help would be appreciated! Thanks!

 

[Joe4]

I find your question a little confusing/unclear, though I am a very visual person.

I think it might be more helpful to me (and others) if you should us some sample of your data, and walked us through an example of what you are trying to do, step-by-step.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Milo2019]

Ok, maybe this will help:
This is a snapshot of the log. When I click on this button highlighted in yellow, It will generate a new change order based off of the template labeled as 'COQ 001' circled in red pen below.

from there I will click the 'link to log' button that is gray so that it will link specific cells from, the new COQ I created to this log I created.

The below highlighted in yellow are some of the cells that will link back to the log shown above.

Question 1
I need to make that into a single button instead of two. The macros are listed in the first post.

Question 2
As it is currently linked to COQ 001, How can I make each new COQ sheet that I make link to the log in the first snip it?

Any help would be great, Thanks,