Macro Copy from Workbook to a different Workbook

[Martin Jones]

Martin
Hello,
I would much appreciate it if someone could help with this macro. I am trying to copy a range that contains formulas from one Workbook to another Workbook. This can be easily done with Worksheets that are in the same Workbook. The challenge is the second workbook name changes every month. I would like for the macro to dynamically pick the 2nd name up based on the workbook I have opened.
Here is my current macro and it works on the named files but again the second workbook has a different name every month. I can manually edit the macro with the new name but I would prefer that the macro do that automatically.
Sub Copy_m()
'
' Copy Macro
'
' Keyboard Shortcut: Ctrl+d
'
Windows("Macros for Ad Hoc Expenditure Report-1.xlsm").Activate
Range("T1:X29").Select
Selection.Copy
Windows("NOV Ad Hoc Expenditure Report - fin-test.xlsx").Activate
Range("T1").Select
ActiveSheet.Paste
End Sub

Any help would be much appreciated.
Martin

 

[offthelip]

you could do something like this which asks the user which workbook to copy to

Sub test()
Dim wb As Workbook
For Each wb In Workbooks
    wb.Activate
   answer = MsgBox("do you want copy to this workbook", vbYesNo + vbQuestion)
   If answer = vbYes Then
    Exit For
   End If
Next wb

End Sub