Transfer of sheet values delete formula in cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I am using the code below on a commandbutton.
Can you see an issue with it ??

Rich (BB code):
Private Sub CommandButton1_Click()
  Sheets("EXPENSES (2)").Range("D4").Value = Sheets("EXPENSES (1)").Range("D30").Value
  Sheets("EXPENSES (2)").Range("F4:K4").Value = Sheets("EXPENSES (1)").Range("F30:K30").Value
  Sheets("EXPENSES (2)").Activate
  ActiveSheet.Range("A5").Select
  If Sheets("EXPENSES (2)").Range("K32").Value <> Sheets("EXPENSES (1)").Range("K32").Value Then MsgBox "Balance of sheets incorrect", vbCritical, "K32 CELLS DO NOT MATCH"
End Sub

The code copies the values from cells on one worksheet & then pastes them onto the next worksheet.
Here is the problem.

On the sheet that the values are pasted to in cell K33 is this formula.

Rich (BB code):
=IF(D4="","",SUM('INCOME (1)'!G32 -'EXPENSES (2)'!K32)-MILEAGE!C32)

What now happens is that the fomula is delete & replaced with a value £123,99 etc etc
I should be able to still click the cell & still see the formula in the address bar but its gone.

If i delete the £123.99 value then paste the formula back in the address bar for that cell its not accpted etc.
I mean there is no error message but if i look in the address bar after i have pasted it there its not shown.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top