I need to change from "=E7" to (equals the current value of E7)

[Dr. Darrell]

I have a macro which is recording entries from several cells to to a table
which will be used for a Run Chart. (see code below)

In my Code, I use "=E7, =G7, =F36..." When all data is filled into my Cells
I click a Form Button and the data is saved into the table. After the
machining operation on my next part, I enter data into the same cells. But
since I am using the "=Cell?" formula, the data is chaning in all the
subsequent rows of my table.

Is there a function for the "current value of a cell" (if I do a Special
Paste, I have the option to paste the Value only)?
__________________________________________________________________


Sub SaveRunChartData_101_4900_30Deg()
'
' SaveRunChartData_101_4900_30Deg Macro
' Macro recorded 2/24/2010 by Darrell.Roak
'

'
Dim LastRow As Long
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row + 1
Range("L" & LastRow).Formula = "=E7"
Range("M" & LastRow).Formula = "=G7"
Range("N" & LastRow).Formula = "=F36"
Range("O" & LastRow).Formula = "=E15"
Range("P" & LastRow).Formula = "=E24"
Range("Q" & LastRow).Formula = "=E31"
Range("R" & LastRow).Formula = "=F34"
ActiveWorkbook.Save
End Sub

 

[Mike H]

Hi,

I'm afraid I don't entirely understand what you mean by

I need to change from "=E7" to (equals the current value of E7)

But think you may mean this

Range("L" & LastRow).Value = Range("E7").Value

If you do it this way then once (say) L(n) has been set to the value of E7
if that latter value changes then the value in L(n) will remain constant.

If this is what you need the other changes to the sub folow exactly the same
format
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Dr. Darrell]

You're the best.

Again, you've been very helpful. This macro does seem to be doing what I had
expected from the start. There is one thing I can anticipate, which is move
my table to a different work sheet.

Darrell

 

[Mike H]

Glad I could help.

Copying this table to a new sheet is straightforward. This would copy the
entire table to sheet 3.

Now if you wanted to copy several tables and not overwrite on sheet 3 it's
simply a matter of finding the LastRow on sheet 3 and I guess you know how to
do that from what we've already programmed.


Dim LastRow As Long
Set sht = Sheets("Sheet3")
LastRow = Cells(Cells.Rows.Count, "L").End(xlUp).Row
Range("L3:R" & LastRow).Copy Destination:=sht.Range("A1")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.