Hello

I have a macro that I want to use in 20 different workbooks that gathers data from another excel file that is created daily. Each workbook series name changes monthly.

So I need to have the macro to work regardless of the workbook name. The problem is I need the macro to know to go to the data daily sheet and back to the Main workbook regardless of the Name. Like a default setting to activate workbbook maybe something like

Windows("whereever the macro was is activated from to go back ").Activate

instead of like this in the code

Windows("Main235 Feb 08 Ver 6.xls").Activate

the code I have is below



Sub CopyMain workbook()
'
' CopyMain workbook
'
' Keyboard Shortcut: Ctrl+w
'
    Range("B66").Select
    Workbooks.Open Filename:= _
        "C:\Documents and Settings\datafile22.xls"
    Range("B4:AH51").Select
    Selection.Copy
    Windows("Main235 Feb 08 Ver 6.xls").Activate
    ActiveWindow.WindowState = xlNormal
    Range("B66").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("datafile22.xls").Activate
    ActiveWindow.Close
    Range("A4").Select
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Summary").Select
    Range("A1").Select
End Sub