Help automating updating columns (find and replace) with macros.

[Stacker]

I routinely get files and people here have helped me with automating one component. However I wanna create a macros that allows m to automate the updating of certain worksheets. I am given an excel file with three tabs.

In the first tab i get a lot of hospital data and one of my tasks is to to copy the last column say MG and paste it into the MH. All of the cells in the column follow a certain formula. The top being like so:

=SUM('\\sancifs\Public_Health\Shared\PH Intelligence\Data\PHI_Data_Intelligence_System\1-PHI_DI_SYS_RAW-IMPORT\COVID-19_data\Hospital_reporting\Both Trusts\[270821 HRI.xlsx]Daily sitrep'!$F$8:$F$11)

I have to paste this column into the next and from there ctrl and f and find and replace. So I would find and replace 270821 with 280821 solely in that column.

Is there a way to copy and paste the previous column and then find and replace by incrememnts of 1 day using a macro.

The second and third stuff are fairly quick so I am not bothered

 

[GWteB]

Yeah, really. You are changing a cell with a (new) formula. Excel tries to evaluate that changed formula, meanwhile not bothering about other similar, already existing formulas being not a precedent or dependent of the changed formula.

 

[Stacker]

I thought the text function should convert it so that =TEXT(D$1,"ddmmyy")& " HRI" will be read as 240821 HRI in the long formula

 

[GWteB]

Sure it does, but that's just one part.
In addition to my previous post: this means that if those other workbooks eventually change at a time when the workbook containing these formulas is closed, the latter can never become aware of such a change. So if such a formula has been evaluated once, the resulting value will always be displayed in such a cell, regardless of whether changes took place in the source or not.
In other words: unreliable data! Most worksheet functions (and INDIRECT is one of them) cannot resolve against a closed workbook.
Both source and "formula" workbooks need te be open at the same time to allow Excel to reevaluate the formula automatically.

That's why I suggested in one of my previous posts to think about another concept.

 

[Stacker]

Hmmm. I thought we cracked it.

 

[Peter_SSs]

Ah, did not notice it before, but the XL2BB tool translates square brackets to text on this board (eg. lsqb & rsqb)

A recent upgrade to the underlying forum software had caused this problem. It should be fixed now, including in existing posts.

 

[GWteB]

@Peter_SSs, thanks for the update