Calling a Macro from Another Workbook with Varying Date

[MMR]

Hi, please could someone help with the following code (it is quite simple/basic):

• The code is essentially opening a workbook (which is rolled over daily) - this is a daily file which therefore has a new name daily varying by latest close of business date (i.e. workday - 1)
• Running (calling) a Macro within that workbook - the Macro name also varies daily by latest close of business date
• Copying data from two tabs (namely "IR Delta" and "XCCY Delta" below) after that Macro has run into the current workbook
• Closing and saving that workbook accordingly
• The code works fine for the current date, however, I need to copy and paste each file daily and roll the date over for each close of business date. Thus, the date at the end of the file name varies
• When I have tried to add "Format(Range("cobdate"), "yyyy mm dd")" to wherever in the file paths is needed, I get an error message that the 'Subscript is out of range'
• How do I make the below dates variable accordingly? it works for the 'Workbooks.Open' line of code, but not for any of the ones after

Thanks in advance!

---

Sub GetSensData()

Dim rng As Range
Dim Mail_Date As String
Mail_Date = Range("cobdate")

'Open a workbook

'Open method requires full file path to be referenced.

Workbooks.Open "\\sirius.ants.ad.anplc.co.uk\tradmktrisk\00 - Core Risk Team\Reporting\SNP Rates\P&L\" & Format(Range("cobdate"), "yyyy") & "\" & Format(Range("cobdate"), "mm Mmmm") & "\SNP Rates P&L " & Format(Range("cobdate"), "yyyy mm dd") & " (old vesion).xlsm"

'Run the Full Daily Process macro in Old Version to be copied into New Version

Application.Run "'SNP Rates P&L " & Format(Range("cobdate"), "yyyy mm dd") & " (old vesion).xlsm'!fullDailyProcess"

'Message Box to say 'Done' when ran Macro

MsgBox ("Old Version Macro ran!")

'Copy the IR Delta sheet from Old Version into New Version

Workbooks("SNP Rates P&L 2020 10 01 (old vesion).xlsm").Worksheets("IR Delta").Range("A1:AL9").Copy _
Workbooks("20201001_SNP_Rates_PNL MR.xlsm").Worksheets("IR Delta").Range("A1")

'Worksheets("IR Delta").Activate

'Copy the XCCY Delta sheet from Old Version into New Version

Workbooks("SNP Rates P&L 2020 10 01 (old vesion).xlsm").Worksheets("XCCY Delta").Range("A1:AL7").Copy _
Workbooks("20201001_SNP_Rates_PNL MR.xlsm").Worksheets("XCCY Delta").Range("A1")

Worksheets("XCCY Delta").Activate

'Save and close old version

Workbooks("SNP Rates P&L 2020 10 01 (old vesion).xlsm").Close SaveChanges:=True


End Sub

 

[Norie]

Where are you getting the error?

Have you considered using a variable to reference the workbook that's being opened and then using that variable to get the workbook name so you can run the macro.

Sub GetSensData()
Dim wbSNPRates As Workbook
Dim rng As Range
Dim Mail_Date As String

    Mail_Date = Range("cobdate")

    'Open a workbook

    'Open method requires full file path to be referenced.

    Set wbSNPRates = Workbooks.Open("\\sirius.ants.ad.anplc.co.uk\tradmktrisk\00 - Core Risk Team\Reporting\SNP Rates\P&L\" & Format(Range("cobdate"), "yyyy") & "\" & Format(Range("cobdate"), "mm Mmmm") & "\SNP Rates P&L " & Format(Range("cobdate"), "yyyy mm dd") & " (old vesion).xlsm")

    'Run the Full Daily Process macro in Old Version to be copied into New Version

    Application.Run "'" & wbSNPRates.Name & "'!fullDailyProcess"

    'Message Box to say 'Done' when ran Macro

    MsgBox ("Old Version Macro ran!")

    'Copy the IR Delta sheet from Old Version into New Version

    wbSNPRates.Worksheets("IR Delta").Range("A1:AL9").Copy _
            Workbooks("20201001_SNP_Rates_PNL MR.xlsm").Worksheets("IR Delta").Range("A1")

    'Worksheets("IR Delta").Activate

    'Copy the XCCY Delta sheet from Old Version into New Version

    wbSNPRates.Worksheets("XCCY Delta").Range("A1:AL7").Copy _
            Workbooks("20201001_SNP_Rates_PNL MR.xlsm").Worksheets("XCCY Delta").Range("A1")

    Worksheets("XCCY Delta").Activate

    'Save and close old version

    wbSNPRates.Close SaveChanges:=True


End Sub

P.S. Why not have the code that's in the workbook being opened in the same workbook as the GetSensData sub?

 

[MMR]

Hi, thanks for your help.

I was getting the error when I tried to make the copy from one workbook to the other part dynamic. i.e. when I tried to do the Format(Range) for the latest close of business date within the last parts of the code too. The above runs fine for the current date in question, just not for others

 

[MMR]

Thanks, I have just tried your code and it worked for the same date. However, how can I also make the workbook where the data is being copied into dynamic?

e.g. in the below, it is referring to the wbSNPRates to copy data from, but then it refers to the full other workbook name to paste in. How do I set a new workbook? e.g. would I 'dim wbSNPRatesNew As Workbook', and then would I use 'Set' or was that just for opening workbooks?

'Copy the IR Delta sheet from Old Version into New Version

wbSNPRates.Worksheets("IR Delta").Range("A1:AL9").Copy _
Workbooks("20201001_SNP_Rates_PNL MR.xlsm").Worksheets("IR Delta").Range("A1")

thanks!

 

[Norie]

How many workbooks do you have open when you are doing this?

Which workbook is the GetSensData sub in?