runtime error 1004 vba method range of object _global failed

Copper Contributor

I have this macro running which seems to work great. However, when I go to open a different workbook, it is giving me the above error along with highlighting the below. 


It seems to be telling me that Range("BETH[[#All],[PRIORITY]]") this is my problem, but I don't know how to fix it or what is wrong. 



1 Reply


Based on the error message and the range reference you provided ("BETH[[#All],[PRIORITY]]"), it appears that you are using a structured reference within a table.

However, structured references are only valid within the same workbook or worksheet where the table is located.

If you are trying to reference a table in a different workbook, you need to modify the range reference to use the workbook and worksheet names, along with the table name.

Here's an example of how you can adjust the range reference:

' Assuming you want to reference the table named "Table1" in the worksheet named "Sheet1" in a different workbook named "Book2.xlsx"

Make sure to replace "Book2.xlsx" with the actual name of the workbook, "Sheet1" with the name of the worksheet, and "Table1" with the name of the table in your specific scenario.

By explicitly specifying the workbook and worksheet names, along with the table name, you can access the desired range even when working with different workbooks.

If you need further assistance, please provide more details (not only a picture) about your VBA code and the context in which you are using the range reference. 

Additional information such as Excel version, operating system, storage medium, etc. would be an advantage.