SOLVED

Saving updated cell values in new cells before it refreshes.

%3CLINGO-SUB%20id%3D%22lingo-sub-144838%22%20slang%3D%22en-US%22%3ESaving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-144838%22%20slang%3D%22en-US%22%3E%3CH3%20id%3D%22threadQuestionHeadingQENUS%22%20class%3D%22c-heading-3%22%20id%3D%22toc-hId-1423259531%22%20id%3D%22toc-hId-1593150041%22%3ESaving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FH3%3E%0A%3CDIV%20class%3D%22thread-message-content-body-text%20thread-full-message%22%3E%0A%3CP%3EI%20have%20a%20spreadsheet%20that%20updates%20values%20in%20specific%20cells%20taken%20from%20the%20web%2C%20every%20hour%2C%20using%20web%20query.%20I%20want%20to%20be%20able%20to%20save%20each%20value%20before%20it%20updates%2Frefreshes%20and%20add%20it%20last%20in%20a%20table%20to%20be%20able%20to%20use%20it%20for%20statistics.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%2C%20after%20one%20day%20I%20would%20have%2024%20different%20values%20saved%20in%20a%20table.%20See%20the%20picture%20below%2C%20the%20value%2010%20in%20%22Updating%20value%22%20should%20be%20saved%20in%20the%20cell%20under%207%20before%20it%20updates.%20Then%20continuing%20to%20add%20values%20further%20down%20for%20each%20hour.%20(This%20is%20not%20my%20real%20spreadsheet%20just%20an%20example)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20title%3D%22%22%20href%3D%22https%3A%2F%2Ffud.community.services.support.microsoft.com%2FFud%2FFileDownloadHandler.ashx%3Ffid%3D4b883d69-9d54-4375-9106-f0ceb2632930%22%20target%3D%22_blank%22%20rel%3D%22noreferrer%20noopener%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ffud.community.services.support.microsoft.com%2FFud%2FFileDownloadHandler.ashx%3Ffid%3D4b883d69-9d54-4375-9106-f0ceb2632930%22%20border%3D%220%22%20alt%3D%22Image%22%20%2F%3E%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWould%20really%20appreciate%26nbsp%3Bif%20someone%20could%20help%20me%20out!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22%22%3E%2F%2FEmil%3C%2FP%3E%0A%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-144838%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145827%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145827%22%20slang%3D%22en-US%22%3EYou're%20welcome!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145610%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145610%22%20slang%3D%22en-US%22%3E%3CP%3EThanks!%20Worked%20flawlessly!%20%3AD%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145458%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145458%22%20slang%3D%22en-US%22%3E1.%20Make%20sure%20Excel%20calculates%20when%20cell%20A5%20changes%20by%20placing%20a%20formula%20like%20%3DA5%20in%20any%20cell%20on%20that%20sheet%3CBR%20%2F%3E2.%20Right-click%20the%20sheet's%20tab%20and%20choose%20%22View%20code%22%3CBR%20%2F%3E3.%20paste%20this%20code%3A%3CBR%20%2F%3EPrivate%20Sub%20Worksheet_Calculate()%3CBR%20%2F%3E%20Application.EnableEvents%20%3D%20False%3CBR%20%2F%3E%20Me.Range(%22M%22%20%26amp%3B%20Me.Rows.Count).End(xlUp).Offset(1).Value%20%3D%20Me.Range(%22A5%22).Value%3CBR%20%2F%3E%20Application.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3EMind%20you%2C%20this%20also%20will%20copy%20the%20value%20of%20cell%20A5%20if%20you%20manually%20hit%20F9%20or%20if%20something%20else%20causes%20a%20calculation%20in%20our%20file.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145250%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145250%22%20slang%3D%22en-US%22%3E%3CP%3EIt%20is%20triggered%20by%20a%20web%20query%20that%20refreshes%20every%2060%20minutes.%20The%20cell%20value%20for%20A5%20is%20changed%20every%2060%20minutes.%20I%20want%20to%20be%20able%20to%20save%20every%20value%20in%20other%20cells%20that%20don't%20refresh.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-145181%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20updated%20cell%20values%20in%20new%20cells%20before%20it%20refreshes.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-145181%22%20slang%3D%22en-US%22%3EHow%20is%20this%20update%20triggered%20precisely%3F%3C%2FLINGO-BODY%3E
New Contributor

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 each value before it updates/refreshes and add it last in a table to be able to use it for statistics.

 

For example, after one day I would have 24 different values saved in a table. See the picture below, the value 10 in "Updating value" should be saved in the cell under 7 before it updates. Then continuing to add values further down for each hour. (This is not my real spreadsheet just an example)

 

Image

 

Would really appreciate if someone could help me out!

 

//Emil

8 Replies
How is this update triggered precisely?

It is triggered by a web query that refreshes every 60 minutes. The cell value for A5 is changed every 60 minutes. I want to be able to save every value in other cells that don't refresh.

best response confirmed by Emil Larsson (New Contributor)
Solution
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.

Thanks! Worked flawlessly! :D

@Jan Karel Pieterse 

How do I change the macro if I have data in cells B5, C5 and D5 as well?

@LisaT760 That would become something like this:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Resize(,4).Value = Me.Range("A5:D5").Value
    Application.EnableEvents = True
End Sub

@Jan Karel Pieterse 

 

Hi! 

 

I'm utilising your bit of code and it works great, however I want to write the values to another sheet (like a data dump style thing)

 

I tried modifying your code from this:

 

Me.Range("M" & Me.Rows.Count).End(xlUp).Offset(1).Resize(,4).Value = Me.Range("A5:D5").Value

 

to this:

 

Me.Range("OtherWorksheet!M" & Me.Rows.Count).End(xlUp).Offset(1).Resize(,4).Value = Me.Range("A5:D5").Value

 

Trying to get it to reference and point to another sheet, however I can't get it to work. Any ideas? It gives me compile errors and things like that.