How to copy data from one file and paste it into a dynamically named workbook

[healeydm]

I am currently creating a macro to improve a weekly file update process. I have to copy a specific range of data from one file (file name "Previous Weeks File") and paste it into my current week file - the new week's file name has date changes: "File Update - 03-17 (Weekly)". How can I copy the data from the "Previous Weeks File" and into the dynamically named workbook I'm updating?

I've tried to use the ThisWorkbook function, but it only works when it's being used in the original file I created the macro in. This macro will be used in a new file every week, so that function isn't a viable solution. I also plan to send this macro to the process owner once it's completed so they can use this to make their weekly update process more time efficient.

Windows("Previous Weeks File.xlsx").Activate
    Sheets("Summary").Select
    Range("G7:G9").Select
    Selection.Copy
ThisWorkbook.Activate
    Sheets("Summary").Select
    Range("H7:H9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[dermie_72]

If you're in the new workbook at the time you run the macro you can set a variable to that workbook
IE.

Set Currentweek = Activeworkbook
Windows("Previous Weeks File.xlsx").Activate
Currentweek.Sheets("Summary").Range("H7:H9").value = Sheets("Summary").Range("G7:G9").Value

Set Currentweek = Nothing

if that doesn't work try this:

Set Currentweek = Activeworkbook
Windows("Previous Weeks File.xlsx").Activate
    Sheets("Summary").Select
    Range("G7:G9").Select
    Selection.Copy
Currentweek.Activate
    Sheets("Summary").Select
    Range("H7:H9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Set Currentweek = Nothing

 

[healeydm]

If you're in the new workbook at the time you run the macro you can set a variable to that workbook
IE.

Set Currentweek = Activeworkbook
Windows("Previous Weeks File.xlsx").Activate
Currentweek.Sheets("Summary").Range("H7:H9").value = Sheets("Summary").Range("G7:G9").Value

Set Currentweek = Nothing

if that doesn't work try this:

Set Currentweek = Activeworkbook
Windows("Previous Weeks File.xlsx").Activate
    Sheets("Summary").Select
    Range("G7:G9").Select
    Selection.Copy
Currentweek.Activate
    Sheets("Summary").Select
    Range("H7:H9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Set Currentweek = Nothing

Thank you so much - both worked beautifully! The only issue I'm having is after copying from "Previous Weeks File" to the dynamically named file, the "Previous Weeks File" is now the active workbook and I'm not able to get back to the dynamic file and continue the macro. Any advice on how to correct this?

 

[dermie_72]

Thank you so much - both worked beautifully! The only issue I'm having is after copying from "Previous Weeks File" to the dynamically named file, the "Previous Weeks File" is now the active workbook and I'm not able to get back to the dynamic file and continue the macro. Any advice on how to correct this?

Set Currentweek = Activeworkbook
Windows("Previous Weeks File.xlsx").Activate
     Set previousweek = activeworkbook
    Sheets("Summary").Select
    Range("G7:G9").Select
    Selection.Copy
Currentweek.Activate
    Sheets("Summary").Select
    Range("H7:H9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
previousweek.activate
Set Currentweek = Nothing
Set previousweek = nothing

This will revert to previous week once the paste has occurred