Absolute Reference Formula giving wrong value while copying from another worksheet

[PritishS]

Dear Sir/Madam,

I'm new to VBA. Trying to learn from internet and previously got best response here at Mr. Excel. I have got a wired kind of problem.

a. I have a worksheet named 'Template'. there I have a table template like below shown.
b. Here 'TOTAL QTY' column have a formula F4=E4*$C$2*$E$2
F5=E5*$C$2*$E$2


A B C D E F
[TABLE="width: 732, align: left"]
<tbody>[TR]
[TD]SWITCHBOARD NAME
[/TD]
[TD] PCC PANEL
[/TD]
[TD]BOARD QTY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]FEEDER NAME
[/TD]
[TD] 630A MCCB Incomer
[/TD]
[TD]1
[/TD]
[TD]FQTY
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BOQDESCRIPTION
[/TD]
[TD]MAKE
[/TD]
[TD]CATALOG NUMBER
[/TD]
[TD]PRICE
[/TD]
[TD]QTY
[/TD]
[TD]TOTAL QTY
[/TD]
[/TR]
[TR]
[TD]630A 4P 55kA MCCB
[/TD]
[TD]AVK
[/TD]
[TD]3VL5763-1TN4
[/TD]
[TD]88390
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Rotary drive
[/TD]
[TD]AVK
[/TD]
[TD]3VL9600-3HQ00
[/TD]
[TD]3530
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]










c. In another worksheet name 'BAT', I have a button called 'AddTemplate'. When I click this button this above shown entire template table get copied and pasted in 'BAT' worksheet.
d. My problem is when it first time get pasted, it's okay. but second time when I again click on 'AddTemplate' button, table template gets added after last used row. In this case TOTAL QTY absolute formula takes previous table's cell reference.

A B C D E F
[TABLE="width: 732, align: left"]
<tbody>[TR]
[TD]SWITCHBOARD NAME
[/TD]
[TD] PCC PANEL
[/TD]
[TD]BOARD QTY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]FEEDER NAME
[/TD]
[TD] 630A MCCB Incomer
[/TD]
[TD]1
[/TD]
[TD]FQTY
[/TD]
[TD]2
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BOQDESCRIPTION
[/TD]
[TD]MAKE
[/TD]
[TD]CATALOG NUMBER
[/TD]
[TD]PRICE
[/TD]
[TD]QTY
[/TD]
[TD]TOTAL QTY
[/TD]
[/TR]
[TR]
[TD]630A 4P 55kA MCCB
[/TD]
[TD]AVK
[/TD]
[TD]3VL5763-1TN4
[/TD]
[TD]88390
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Rotary drive
[/TD]
[TD]AVK
[/TD]
[TD]3VL9600-3HQ00
[/TD]
[TD]3530
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]








[TABLE="width: 732, align: left"]
<tbody>[TR]
[TD]SWITCHBOARD NAME
[/TD]
[TD] PCC PANEL
[/TD]
[TD]BOARD QTY
[/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]FEEDER NAME
[/TD]
[TD] 630A MCCB Incomer
[/TD]
[TD]1
[/TD]
[TD]FQTY
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]BOQDESCRIPTION
[/TD]
[TD]MAKE
[/TD]
[TD]CATALOG NUMBER
[/TD]
[TD]PRICE
[/TD]
[TD]QTY
[/TD]
[TD]TOTAL QTY
[/TD]
[/TR]
[TR]
[TD]630A 4P 55kA MCCB
[/TD]
[TD]AVK
[/TD]
[TD]3VL5763-1TN4
[/TD]
[TD]88390
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Rotary drive
[/TD]
[TD]AVK
[/TD]
[TD]3VL9600-3HQ00
[/TD]
[TD]3530
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]










For second Table template 'TOTAL QTY' should be F9=E9*$C$7*$E7$ & F10=E10*$C$7*$E7$
Instead of that the formula is F9=E9*$C$2*$E$2 & F10=E10*$C$2*$E$2

Is there any possibility to achieve this?

Temporarily I have made an arrangement. In 'Template' sheet I wrote the formula without $ like F4=E4*C2*E2
And when the formula have relative reference, I'm getting result as expected.

Please suggest me is there anyway even after copy and paste the template table from 'Template' sheet to 'BAT' sheet.

Help me please!

Thanks
PritishS

 

[mikerickson]

Do you copy/paste or Cut/Paste?
If you Cut/Paste all absolute references to the Cut cells will be adjusted for their new location.

 

[PritishS]

Do you copy/paste or Cut/Paste?
If you Cut/Paste all absolute references to the Cut cells will be adjusted for their new location.

Hello Sir,

I'm doing copy/paste using a macro, which i have assigned to 'AddTemplate' Button.
Shall i try with Cut/Paste? Actually I can not do cut/paste to that table in template as i'm using it as template for future use.

Any suggestion?

Thanks for your valuable time.

Thanks
PritishS

 

[mikerickson]

No, I'm saying do not cut and paste.

 

[PritishS]

Do you copy/paste or Cut/Paste?
If you Cut/Paste all absolute references to the Cut cells will be adjusted for their new location.

Dear Sir,

As you suggested, I've tried to Cut/Paste the Template table to 'BAT' worksheet with absolute cell reference and as you said-"all absolute references to the Cut cells will be adjusted for their new location", it does happen. But I'm little confused with your last comment-"No, I'm saying do not cut and paste." Here you are saying not to do CUT/PASTE?
If I Copy/Paste the Absolute cell references are not adjusting to their new location. can you please clarify a bit more?

Thanks
PritishS

 

[PritishS]

Hello Sir,

I'm glad to inform you that your suggestion to cut that table from 'Template' sheet and Paste it in 'BAT' page after last use row is working as expected. Now all the absolute formulas changed as per their new location.
I did a little trick. As I said this table in template for future reference. So I made another similar table down in the same 'Template' Sheet and after cut paste I just copied the below table and paste it back in blank space of previous table.

Thank you Very Much for your guidance. Wish you a Good Luck!

Thanks
PritishS