"#REF! error" when deleting rows in source sheet

SD-2023 Can't really imagine why you would use macro's to do data cleaning and transformations. Power Query is the right tool for that. You mention Sheet1 and Sheet2 but the formula refers to a Sheet3.  How does Sheet3 relate to Sheet1 and Sheet2?

Now, when you directly refer to a cell, let's say M3, as soon as your macro deletes that cell or the entire row 3 or the entire column M, you will get #REF!. There's nothing you can do about that. You would have to work with INDEX and MATCH functions where you look for matching row and column headers in a certain range. Or you could work out an OFFSET function where you look for the cell 2 rows down and 12 columns right from cell A1, which would be M3.

However, Excel is smart enough to automatically update a direct cell reference the when you delete or insert rows/columns above/to the left of the cell.