Need to reference specific columns between multiple worksheets using VBA code

Hello Experts,

I am VBA self taught so I am probably overthinking this.

I am trying to populate a pre-formatted table in one worksheet with data computed from a different worksheet using VBA and sumifs() function.

The code works when the columns are referenced using for example C[31], C[2], etc. The problem here is that once i move to entering numbers in a different column in my table, all the references shift. I need the referenced columns to remain the same! I have been unsuccessful trying to reference the columns by the column headers (i must not know how to do this correctly). Below code should summarize the issue:

Sub Test1_Dashboard_Calc()

'This will populate first cell in pre-formatted table

Please Login or Register  to view this content.

With the above example, I expected that the referenced columns will be fixed but this is not the case ( C[58] is now referencing C[61])

Please help. A solution using column headers directly would be ideal

- Don't want to define any variables if possible

If you want absolute references, try using C58.

unfortunately it doesnt look like that works with the sumifs function when used with VBA