How to get (range)macro to always run code on a specific sheet regardless of active sheet?

I am very new to macros still and need some help with this macro i'm writing. One to get it to always operate on a specific sheet and two to see if what i've written so far is correct or if i'm missing something somewhere that will cause it to break unexpectedly.

I took this macro from here:

Sub GetRealLastCell()
  Dim lRealLastRow As Long
  Dim lRealLastColumn As Long
  Range("A1").Select
  On Error Resume Next
  lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
                                               xlPrevious).Row
  lRealLastColumn = Cells.Find ("*",Range("A1"), xlFormulas, , _
                                    xlByColumns, xlPrevious).Column
  Cells(lRealLastRow, lRealLastColumn).Select
End Sub

It finds the last used cell on a spreadsheet.

I then modified it into this:

Sub RefreshTPRanges()
  Dim lRealFirstRow As Long
  Dim lRealFirstColumn As Long
  Dim lRealLastRow As Long
  Dim lRealLastColumn As Long
  Dim TopLeft As Range
  Dim BottomRight As Range
  Dim TopRight As Range
  Dim rTPDATA As Range
  Dim rTPHEADERS As Range
  
  Range("A1").Select
  
  On Error Resume Next
  
  lRealFirstRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
                                               xlNext).Row
  lRealFirstColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
                                    xlByColumns, xlNext).Column
  lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
                                               xlPrevious).Row
  lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, , _
                                    xlByColumns, xlPrevious).Column
  
  Set TopLeft = Cells(lRealFirstRow, lRealFirstColumn)
  Set BottomRight = Cells(lRealLastRow, lRealLastColumn)
  Set TopRight = Cells(lRealFirstRow, lRealLastColumn)
  
  Set rTPDATA = Range(TopLeft, BottomRight)
  Set rTPHEADERS = Range(TopLeft, TopRight)
  
  
  ActiveWorkbook.Names.Add Name:="TPDATA", RefersTo:=rTPDATA
    ActiveWorkbook.Names("TPDATA").Comment = ""
  
  ActiveWorkbook.Names.Add Name:="TPHEADERS", RefersTo:=rTPHEADERS
    ActiveWorkbook.Names("TPHEADERS").Comment = ""

End Sub

Purpose and goal of macro

It finds the first row and column and the last row and column and then using that i create two ranges, one that encompasses all the data and one that encompasses just the header. So the macro finds the block of data and creates two named ranges out of it. I know i can just create a dynamic named range however that doesn't handle blank rows well and gets calculation time intensive when referenced many times (200k +). What this macro does is when ran it updates/expands the rows and columns of your named ranges to include all the newest data you entered. So it dynamically creates a static range. Thus giving me all the benefits of dynamic ranges (not having to manually update my range parameters every time i add more data, just click a button now) without all the processing overhead.

Questions:

1. Is this part in the beginning of the macro necessary:

Range("A1").Select

It doesn't seem to matter when i take it out but the original macro i modified had it and i don't want to do something that will unexpectedly cause it to break. Maybe it has some purpose i don't understand because i'm so new to macros?


2. In this bit of code:

lRealFirstRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, _
                                               xlNext).Row

Does the "xlFormulas" part have to be specified, again something that didn't seem to make a difference when i removed it and I saw elsewhere people were leaving it blank but I don't know if doing so would make my macro more open/capable or limit it's functionality.


3. Lastly and the big question:

If you open the attached spreadsheet and view the formulas in Table 1 you can see that the first House references the named ranges(significantly faster when used many times) while the second house just references the worksheet. Now if you view the named ranges you will see that I created them originally to be dynamic.

Now go to the tab "Timephased Data" and execute the macro or hit ctrl + shift + Z. This will change the two named ranges "TPDATA" & "TPHEADERS" from being dynamic ranges to being a statically defined range. Go back to the "Table" worksheet and you can see that everything is still fine and functioning properly. Now click the macro button at the top of the "Table" worksheet and you can see all the formulas that reference the named ranges are now giving errors.

this is because the macro is executing on the "Table" worksheet, rather than on the "Timephased Data" worksheet.

What I want is for the macro to always run/execute it's code and find/update the data range on the "Timephased Data" worksheet regardless of what worksheet I'm on when i click the button or run the macro. So even if i run the macro from sheet "Table" it should execute and find the data range on sheet "Timephased Data" and do so without ever leaving/deactivating the current worksheet i'm on.

I've tried using the:

With Sheets("Timephased Data")
Code
End with

But it doesn't work. Can anyone offer any help or insight into the above questions.

Thanks

A non-sheetspecific code reference might read:

Range("A1") = "Date"
Range("B1") = Now
Range("A2") = "Name"
Range("B2") = Environ("UserName")

If you wanted to use that code from anywhere and have it still always occur on a specific sheet, then you could do this to it:

With Sheets("Sheet1")
    .Range("A1") = "Date"
    .Range("B1") = Now
    .Range("A2") = "Name"
    .Range("B2") = Environ("UserName")
End With

See how the period is used to "continue" the command from the WITH statement? That is thus equivalent to having written:

Sheets("Sheet1").Range("A1") = "Date"
Sheets("Sheet1").Range("B1") = Now
Sheets("Sheet1").Range("A2") = "Name"
Sheets("Sheet1").Range("B2") = Environ("UserName")

Never use the FIND method without including the the LookAt and the LookIn parameters. Unlike other VBA functions where there is a default, this function does NOT. So leaving those parameters out means Excel will use the settings from the last time it was used. Egad.


If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.