Making a macro on a template update with new each new sheet create
 

I recorded a macro on a template I created to transfer data from a cell on
the template I used onto the next available cell in a column in a different
workbook.

However I have done something wrong because when I open a new file off of
the template I created it doesn't transfer data off the new file - it copies
it off the template (which has no data in it so it copies 0).

Does anyone know how to make a macro update with the file it is in?

This is the macro I recorded:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 10/08/2009 by MDouse
'

'
Workbooks.Open Filename:="\\server1\Profiles\MDouse\My
Documents\Book1.xls", _
UpdateLinks:=0
Columns("A:A").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False, SearchFormat:=False).Activate
ActiveCell.Replace What:="",
Replacement:="=[templatetest.xlt]Sheet1!$A$2" _
, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Selection.FindNext(After:=ActiveCell).Activate
Range("G1").Select
ActiveWorkbook.Save
ActiveWindow.Close
Range("A2").Select
End Sub