I have a very large workbook that imports many worksheets from various places. This is necessary, no linking is possible. A macro runs, deletes the worksheets from the previous day and copies in the new ones. I have several hundred cells that are pulling using a complicated formula, and when the sheets are deleted, it causes the formula to get refilled with a #REF reference to the deleted sheet... even though the sheet is replaced with the same name.
The formula is like this:
So I had this idea of creating my own Intersect Function to feed it the sheet name, the RptDate, and the tag to search for, in this case "ASA". The problem is that I can't use Match in a function.
I had this but it bombs on the Match:
Function ISectPhone(Sheetname, RowTarget, ColTarget) As Variant
Dim sSheetRef As String
Dim sRowRange As String
Dim sColRange As String
sSheetRef = Sheetname & "!A:P"
sRowRange= Sheetname & "!B:B"
sColRange= Sheetname & "!2:2"
ISectPhone = Index(sSheetRef, Match(RowTarget, sRowRange, 0), Match("ASA", sColRange, 0))
I would call it like this =ISectPhone("MySheetThatGetsDeleted",RptDate,"ASA")
But maybe there is an easier way... let me know any brilliant ideas. One thing I did try was linking the sheets instead of deleting and re-copying.. problem then is it takes a half an hour for the workbook to open. Running the macro to delete and recopy takes only a minute.