copy data from source file to destination file, but the source file changes weekly

  • Hi Guys, hoping someone can help me.


    i want to set a macro so that i copy a range from one excel file to another, however the source file name will change weekly, is there any way to have a macro that says something like, 'copy from file that begins with...' ? the current code i have is below:


    Sub Macro2()

    '

    ' Macro2 Macro

    Workbooks("LS-GB_mbu_intl_rpt_20210510.xlsx").Worksheets("Export").Range("A2:D9").Copy

    Workbooks("Reports.xlsm").Worksheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues

    End Sub



    name of the macro is macro2

    source file is called LS-GB_mbu_intl_rpt_20210510.xlsx, however the numbers at the end will change every week.

    destination file is called Reports.xlsm


    many thanks in advance!

  • What is the date part of the name referring to?

    Hi Roy, that's the date i receive the updated data file, every monday.


    so next week i would expect the file i need to copy & paste data from to have the name LS-GB_mbu_intl_rpt_20210517.xlsx


    hope that answers your question

  • Place this macro in the Report workbook. Make sure that the two workbooks in question are the only two workbooks that are open.

    Code
    1. Sub Macro2()
    2. Dim wb As Workbook
    3. For Each wb In Workbooks
    4. If wb.Name <> ThisWorkbook.Name Then
    5. wb.Sheets("Export").Range("A2:D9").Copy
    6. Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
    7. End If
    8. Next wb
    9. Application.CutCopyMode = False
    10. End Sub

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • So assuming you run the code on Monday then maybe


  • Place this macro in the Report workbook. Make sure that the two workbooks in question are the only two workbooks that are open.

    Code
    1. Sub Macro2()
    2. Dim wb As Workbook
    3. For Each wb In Workbooks
    4. If wb.Name <> ThisWorkbook.Name Then
    5. wb.Sheets("Export").Range("A2:D9").Copy
    6. Sheets("Data").Range("A2").PasteSpecial Paste:=xlPasteValues
    7. End If
    8. Next wb
    9. Application.CutCopyMode = False
    10. End Sub

    That's genius, thank you Mumps that's ideal.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.