How do I change a Macro to use ThisWorkbook properly?

Hi,

I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.

The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).

Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.

What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.

I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.

Any help would be greatly appreciated. Here's the code...

Sub UpdateInventory()
'
' UpdateInventory Macro
'
' Keyboard Shortcut: Ctrl+i
'
    Workbooks.Open Filename:= _
        "N:\Accounting\userdata\EXTRA\EXSHARE\INVENTORY\New Inventory project 2014\johnS Inventory test.xls"
    ActiveWindow.SmallScroll Down:=-18
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("4:4").Select
    Selection.Copy
    Rows("3:3").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3"
    Range("B3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R7C6"
    Range("C3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R8C7"
    Range("D3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R8C3"
    Range("E3").Select
    ActiveCell.FormulaR1C1 = "E"
    Range("F3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R6C5"
    Range("G3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R3C5"
    Range("H3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R3C8"
    Range("I3").Select
    ActiveCell.FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R22C6"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R50C13=0,"""",'[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R50C13)"
    Range("K3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R47C3=0,"""",'[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R47C3)"
    Range("L3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[1]="""",""Active"", ""Closed"")"
    Range("M3").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('[Control Sheets (JOHN TEST).xls]Closeout control_ Pg 4'!R40C10=0,"""",'[Control Sheets (JOHN TEST).xls]Closeout control_ Pg 4'!R40C10)"
    Range("M4").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Thanks

Dim wb as workbook
Set wb = Workbooks.Open Filename:= _
        "N:\Accounting\userdata\EXTRA\EXSHARE\INVENTORY\New Inventory project 2014\johnS Inventory test.xls"

Now wb will always refer to that specific workbook within your code. you can us it in many ways e.g. wb.close,

You can do the same with a specific sheet...

Dim wb as workbook
Dim ws as worksheet
Set wb = Workbooks.Open Filename:= _
        "N:\Accounting\userdata\EXTRA\EXSHARE\INVENTORY\New Inventory project 2014\johnS Inventory test.xls"
Set ws = wb.worksheets("sheet name")

This gives you the ability to refer to exactly that sheet. Examples of use include:

ws.range("a4").value   'refers to the value of cell A4 within that sheet 

'or, just a guess...

ws.range("A3")FormulaR1C1 = _
        "='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3"

Hopefully this helps you get started.

Change this...
"='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3"

To this...
"='[" & ThisWorkbook.Name & "]FUND SET_UP PG_1'!R2C3"

Workbooks.Open Filename:= _
        "N:\Accounting\userdata\EXTRA\EXSHARE\INVENTORY\New Inventory project 2014\johnS Inventory test.xls", _
        UpdateLinks:=3

TIP: in the VBA editor, highlight the Open keyword in the code and press F1 for help\syntax\example code.

The second part is too vague; what exists where? How is it tested for?