Coding issue

[seahawk56]

I have created a template that has a macro that pulls data from a spread sheet (Monday) and pastes in another spread sheet (Prodweek). When I adjust the template to be updated for a new week. I just do the find and replace with the name (Prodweek) and the date span of the week. I am by no means efficient in Visual Basic but I feel comfortable doing the find and replace function. But the problem is that a few people will be using this that are not very versed in Visual Basic. So there is some concern they might mess things up or feel overwhelmed messing around in there. I have a friend that was telling me he has seen before where by entering in the desired name (name of the new spread sheet) in a cell that it will adjust the coding to the new name. We were trying to figure it out but could not get it to work. Below I'm pasting the code that we have so far. If anyone could please help out it would be greatly appreciated.
Thank you,
Seahawk56

Option Explicit
Const sheetname1 = "KaizenBoard"
Dim Prodweek As String
Sub MondayProd()
'
' MondayProd Macro
' Macro recorded 9/20/2008 by whitjh1
'
Prodweek = Trim(Sheets(sheetname1).Cells(2, 1).Value)
Workbooks.Open Filename:= _
"G:\ROC-CLAIMS\Clms Proc-Model Line\POSKaizenReports\Production Results\Monday.xls"
Sheets("Data").Select
Selection.ClearContents
Selection.QueryTable.Delete
Application.Run "Monday.xls!Prod"
Sheets("Daily Prod").Select
Range("B2:B25").Select
Selection.Copy
Windows(Prodweek & ".xls").Activate
ActiveWindow.SmallScroll Down:=-12
Range("BS3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Monday.xls").Activate
ActiveWindow.SmallScroll Down:=-27
Range("D2:E25").Select
Application.CutCopyMode = False
Selection.Copy
Windows(Prodweek & ".xls").Activate
ActiveWindow.SmallScroll Down:=-9
Range("BW3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Monday.xls").Activate
ActiveWindow.SmallScroll Down:=-21
Range("H2:H3").Select
Application.CutCopyMode = False
Selection.Copy
Windows(Prodweek & ".xls").Activate
Sheets("Kaizen Board").Select
Range("B22").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub