Forum Discussion
Correct use of Worksheets statement in VBA
I wonder whether opening the target workbook and then working step by step might provide a starting point.
Set wb = Workbooks("Genealogical_Service_Users_Tracing_Project_Families.xlsm")
Set ws = wb.Worksheets("Names in Alpha Order")
Test_Surname = ws.Cells(3, 4).ValueSomething radically different might be to ditch the usual sheet name and cell address approach and go for a Table using Power Query?
I wonder whether opening the target workbook and then working step by step might provide a starting point.
Set wb = Workbooks("Genealogical_Service_Users_Tracing_Project_Families.xlsm")
Set ws = wb.Worksheets("Names in Alpha Order")
Test_Surname = ws.Cells(3, 4).Value
Something radically different might be to ditch the usual sheet name and cell address approach and go for a Table using Power Query?
Dear Peter
Thank you for pointing me in the right direction. I eventually wrote two other small functions which stripped the CELL return value down to just the workbook and worksheet names, respectively. This then made the central essence of the function work. I could therefore remove all the string manipulation from the 'main' function.
Best
Philip