How to reference ranges/columns of a table in an inactive sheet

Question

I'm trying to reference a data range table on an inactive sheet on Excel with VBA.

I can reference the sheet by making it active:

Sheets("DTC").Select

    ActiveSheet.ListObjects("DTC_Table").ListColumns("Delay").DataBodyRange.Select
    ActiveCell.Formula = "=IF(--IsColour(0,K2),""Active"",(IF(--IsColour(2500134,K2),""Active"",IF(--IsColour(4210752,K2),""Active"",IF(V2=""No"","""",""Inactive"")))))"

but I don't know why referencing an inactive sheet does not seem to work:

Sheets("DTC").ListObjects("DTC_Table").ListColumns("Delay").DataBodyRange.Select
    ActiveCell.Formula = "=IF(--IsColour(0,K2),""Active"",(IF(--IsColour(2500134,K2),""Active"",IF(--IsColour(4210752,K2),""Active"",IF(V2=""No"","""",""Inactive"")))))

Thanks very much.

You can't `Select` something that isn't on the `ActiveSheet`. — dwirony, Mar 28 '19 at 16:21

score 0 · Accepted Answer · answered Mar 28 '19 at 16:52

0

You don't need to do a Select. You already have the reference. Try:

ThisWorkbook.Worksheets(<your sheet name here>)
    .ListObjects("DTC_Table").ListColumns("Delay").DataBodyRange.Formula = <your formula here>
End With

Might be worth reading why we shouldn't use things like Select and Activate

answered Mar 28 '19 at 16:52

Zac

1,924
1
8
21

Thank you. I found the below, which I will read. https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Luke Sharkey Mar 28 '19 at 17:10

How to reference ranges/columns of a table in an inactive sheet

1 Answers1