Hello,
Hope you are having good-day
I have a complicated question
I have a multiple changing values in multiple sheets what I want is to make a button that type all numbers that are connected to foumula to non formula cells for example:
excel.jpg
I want a button that when pressed all values that resulted from formula in cells (C33:K33) to be typed to (C2:K2) with no formula and can be removed when needed also I want it to
affect a number of 6 similiar sheets contain the same table simulatanuosly
I searched the web and had this code :
im xVal As String
'Update by Extendoffice 2018/8/22
Private Sub Worksheet_Change(ByVal Target As Range)
Static xCount As Integer
Application.EnableEvents = False
If Target.Address = Range("C2").Address Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount
Else
If xVal <> Range("C2").Value Then
Range("D2").Offset(xCount, 0).Value = xVal
xCount = xCount
End If
End If
Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
xVal = Range("C2").Value
End Sub
But the problems of this code is :
1- It only affect one cell and I want it to affect a range of cells
2- It changes (D2) whenever c2 changes not when i want it to type the number (press the button)
3- it affect only one sheet and I want it to affect 8 sheets
Thanks
M.Nasr
Bookmarks