CODEWITHSUNDEEP
excelvba
Asia Ciaramella
Asia Ciaramella

Reputation: 1

How to change the workbook in a recorded macro with a workbook that changes name every month

I´m quite new to VBA and I´m stuck with a macro. Since a lot of calculations can be done simply through Excel formulas, I just recorded a macro with them. The problem is that all these calculations are linked to workbooks that change names monthly and I would like to know if there exists a way to change only the workbook part in a recorded macro.

I want to avoid to rewrite the formulas only with VBA because they are really a lot in many different workbooks and they are not really streightforward, so it would take a lot of time for me to code them with VBA.

Since as I said the workbooks change names every month, what I did so far was to create a code for opening all the workbooks according to the monthly names. They have all in common a part of the path called in the code "basepath" and then each will have a different ending.

I tried, without succeeding, to put the new name of the workbook into the recorded macro but it didn´t work.

Dim Network As Object
Set Network = CreateObject("wscript.network")

Dim Path As String
Dim basePath As String
basePath = ‘my base path’

Dim Debtbalance _Path As String
Dim Debtbalance_Path As String
Debtbalance_Path = basePath & ‘rest of the path

Dim Debtbalance As Workbook
Set Debtbalance = Application.Workbooks.Open(Debtbalance_Path)

Windows("Test MTD.xlsm").Activate
Sheets("MTD Checks").Select
ActiveCell.FormulaR1C1 = _
"=ROUND(SUMIFS([Monthly Debt Balance_202307.xlsx]Sheet1!C8,[Monthly Debt
Balance_202307.xlsx] Sheet1!C14,"">=";";&R[-3]C[-3],[Monthly Debt
Balance_202307.xlsx] Sheet1!C14,""<=";";&R[-3]C[-2]),0)";

This is just one of all the formulas I recorded as example. I tried to substitute "Monthly Debt Balance_202307.xlsx" with the Workbook "DebtBalance".

Upvotes: 0

Views: 47

Answers (1)

jkpieterse
jkpieterse

Reputation: 2986

Simple!

ActiveCell.FormulaR1C1 = _
"=ROUND(SUMIFS([" & Debtbalance.Name & "]Sheet1!C8,[" & Debtbalance.Name 
& "]Sheet1!C14,"">=""&R[-3]C[-3],[" & Debtbalance.Name & "]Sheet1!C14,""<=""&R[-3]C[-2]),0)"

Upvotes: 0

Related Questions