Can I create a macro that runs the recorded process on newly added rows as well?

[Bo_Suto]

Hello, 

I'm running a report once a week that has changing number of rows every week - same columns but increasing number of rows. I edit the report by adding in new columns with various formulas and then I use the spreadsheet for a Power BI report. As the columns and the formulas I'm adding in manually every week are always the same, I wanted to make the process more efficient and automated, hence I tried creating/recording a macro. Creating it was easy and it seemed to have worked, until I realized that the macro only runs for the number of cells/rows that were there at the moment of the recording of the macro. Since my report has more rows every week, the macro is not working on these new rows, i.e. my formulas are not added in to the new rows. Which is mention in this article:

https://support.microsoft.com/en-us/office/automate-tasks-with-the-macro-recorder-974ef220-f716-4e01... 

My question is whether this could be changed? Is there any way to make it more dynamic somehow? Could the macro somehow recognize the number of rows in the spreadsheet (which will be different every single week) and add in the formulas for all the rows/cells where there is data?

Also, I'm using a PC, Windows 10, Microsoft 365, Excel version 2107 

Many thanks, 

Bo 

[#2]

@Bo_Suto 

Yes, that is very well possible, but it'd be helpful to see the code you have now...

[#3 · Accepted solution]

@Bo_Suto Since you are in to Power BI, forget about VBA and learn PowerQuery in stead. Connect to the weekly data files (Files from Folder will probably work best), do some transformations and create an ever expanding table that feed your Power BI report.

PowerQuery may be a bit overwhelming in the beginning, but you'll love it once you get the hang of it.

https://exceloffthegrid.com/power-query-introduction/ 

[#4]

Thanks a lot for the very useful link @Riny_van_Eekelen, really appreciate it. I've already started working my way through the different chapters :)

[#5]

Thanks for responding @Hans Vogelaar, good to know, however, I've decided to check out PowerQuery instead...