updating macros

[Guest]

i have a few macros that i have made for a blank workbook, i want to keep the workbook as a template, so when i save it, i change the filename. now whenever i run the macros it comes up with that it needs to run the debugger. how do i automatically update the macros so they reference to the current filename? Failing that, is there an easier way to run it so that it activates the current file rather than it having to be named? i am using the following macro:

Sub copyam()
Workbooks.Open Filename:= _
"\\Alc-server\Users\Midshires\My Documents\Wages\Blank Sheets and Lists\Blank Wage Book (Do Not Edit).xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Blank AM").Select
Rows("1:31").Select
Selection.Copy
Windows("Blank Wage Book Macro test.xls").Activate
Range("A65536").End(xlUp)(1).Select
ActiveSheet.Paste
Windows("Blank Wage Book (Do Not Edit).xls").Activate
ActiveWindow.Close
End Sub

i hope this makes sense! Any help would be much appreciated

 

[Dave Peterson]

If you run the code against the activesheet, then maybe you can just keep track:

Option Explicit
Sub copyam()

Dim curWks As Worksheet
Dim doNotEditWkbk As Workbook
Dim destCell As Range

Set curWks = ActiveSheet
Set doNotEditWkbk = Workbooks.Open _
(Filename:= _
"\\Alc-server\Users\Midshires\My Documents\Wages\" & _
"Blank Sheets and Lists\Blank Wage Book (Do Not Edit).xls")

With curWks
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

doNotEditWkbk.Worksheets("blank am").Rows("1:31").Copy _
Destination:=destCell

doNotEditWkbk.Close savechanges:=False

End Sub

I did change the cell that gets pasted, though. I came down one row.

(Untested, but compiled ok.)