T
TKS_Mark
I've modified an existing macro that copies one range to another by pasting
values. The source range has formulas that use CONCATENATE to build a
formula by looking up values in several cells. That part works ok, but when
I paste it as values, I need to edit each cell and change the = to = (in
other words, fool Excel into thinking I've just entered this brilliant
formula on my own. Otherwise, Excel just shows the pasted formula instead of
the results). The code below did it on a range that is some distance away
from the original range. This time, I just want to do it to a specific range
called 'AZInputs[TestPaste]'. What change would I need to make to the code
below in order to make this work?
With myRngToCopy
.Copy
With .Offset(0, -12)
.PasteSpecial Paste:=xlPasteValues
'Selection.Style = "Calculation"
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With
values. The source range has formulas that use CONCATENATE to build a
formula by looking up values in several cells. That part works ok, but when
I paste it as values, I need to edit each cell and change the = to = (in
other words, fool Excel into thinking I've just entered this brilliant
formula on my own. Otherwise, Excel just shows the pasted formula instead of
the results). The code below did it on a range that is some distance away
from the original range. This time, I just want to do it to a specific range
called 'AZInputs[TestPaste]'. What change would I need to make to the code
below in order to make this work?
With myRngToCopy
.Copy
With .Offset(0, -12)
.PasteSpecial Paste:=xlPasteValues
'Selection.Style = "Calculation"
With .Resize(, 2) 'same number of rows, but two columns
.Replace What:="=", _
Replacement:="=", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End With
End With
End With