excel - Add data into table without auto increment -

so trying dynamically insert data excel table other closed workbooks.

i got working fine, except 1 small annoying thing.

i have formula follows:

='h:\dev...[some book name.xlsm]main'!c1

the formula above works fine. need insert exact same formula table in sheet several rows.

it should in 1 column:

='h:\dev...[some book name.xlsm]main'!c1
='h:\dev...[some book name.xlsm]main'!c1
='h:\dev...[some book name.xlsm]main'!c1

what excel does, automatically changes cell references incremental, this:

='h:\dev...[some book name.xlsm]main'!c1
='h:\dev...[some book name.xlsm]main'!c2
='h:\dev...[some book name.xlsm]main'!c3

i insert the formulas string array, , paste table using code:

set lstobj = sheets(1).listobjects(1) set rnglstobj = lstobj.range rnglstobj.offset(1, 0).resize(rnglstobj.rows.count - 1,rnglstobj.columns.count)     .formula = revlist   end 

in code above, revlist 2 dimentional array.

i tried setting .formula, .value, in both cases excel changes cell references incremental.

i tried disabling calculation

thisworkbook.sheets(1).enablecalculation = false 

still same.

how stop behavior vba side?

if start with

='h:\dev...[some book name.xlsm]main'!$c$1 

it force absolute reference wherever copied.

the added dollar signs prevent range changing.