Forum Discussion
Solved
Saving updated cell values in new cells before it refreshes.
Saving updated cell values in new cells before it refreshes.
I have a spreadsheet that updates values in specific cells taken from the web, every hour, using web query. I want to be able to save ...
- 1. Make sure Excel calculates when cell A5 changes by placing a formula like =A5 in any cell on that sheet
2. Right-click the sheet's tab and choose "View code"
3. paste this code:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Value = Me.Range("A5").Value
Application.EnableEvents = True
End Sub
Mind you, this also will copy the value of cell A5 if you manually hit F9 or if something else causes a calculation in our file.
1. Make sure Excel calculates when cell A5 changes by placing a formula like =A5 in any cell on that sheet
2. Right-click the sheet's tab and choose "View code"
3. paste this code:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Value = Me.Range("A5").Value
Application.EnableEvents = True
End Sub
Mind you, this also will copy the value of cell A5 if you manually hit F9 or if something else causes a calculation in our file.
2. Right-click the sheet's tab and choose "View code"
3. paste this code:
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Value = Me.Range("A5").Value
Application.EnableEvents = True
End Sub
Mind you, this also will copy the value of cell A5 if you manually hit F9 or if something else causes a calculation in our file.
How do I change the macro if I have data in cells B5, C5 and D5 as well?