(Solved) – How to activate a workbook that has dates in file name and change weekly?

  • by
(solved)-–-how-to-activate-a-workbook-that-has-dates-in-file-name-and-change-weekly?

I need your help.
I have this main macro that opens a previous day file by using a cell reference (location of the file and file name). The opened file is used to copy the data from its worksheets (4 worksheets)to the main macro. I recorded the macro and the date was hard coded. When i will run the macro for next week, it will give me an error since the previous file name was hardcoded. I tried using activeworkbook but it wont work since i go back and fort between the main file and preivous file when copy pasting.

sample query below

Sub OpenPrev()
'
' OpenPrev Macro
'

'
    ChDir "\hfx1nas02hfx1_data02_sh$Share2HFX MDBGMarlonMarkitEDMEDM_WORK"
    Workbooks.Open Range("M1").Value & ".xlsx"
    Sheets("BBG COMDTY - Cur Data").Select
    Range("A3:AF10000").Select
    Selection.Copy
    Windows("EDM Summary Macro 1.1.xlsm").Activate
    Sheets("BBG COMDTY - Prev Data").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Windows("EDM_Matcher_Inbox_20191202.xlsx").Activate
    Sheets("BBG PK BBGID - Cur Data").Select
    Range("A3:AJ10000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("EDM Summary Macro 1.1.xlsm").Activate
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    ActiveWindow.ScrollWorkbookTabs Sheets:=1
    Sheets("BBG PK BBGID - Prev Data").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveWorkbook.Activate
    Windows("EDM_Matcher_Inbox_20191202.xlsx").Activate
    Sheets("BBG BO - Cur Data").Select
    Range("A3:AH10000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("EDM Summary Macro 1.1.xlsm").Activate
    ActiveWindow.ScrollWorkbookTabs Sheets:=2
    Sheets("BBG BO - Prev Data").Select

Leave a Reply

Your email address will not be published. Required fields are marked *