• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to create a macro that copy pastes a fix range into another sheet and takes into consideration a cell value

C

CuriousFox

New Member
Hello!

I'm pulling from Yahoo Finance the historical stock prices. You can only get with Power Query 100 lines. I have downloaded the history in one sheet and I have the Power Query with the last 100 days in another sheet. I would like to have a macro that pastes the new values into the history sheet only if they come after the values already saved in the history.

For example below in Table 0 it should only copy into Sheet1 the last month:

1667323082773.png

1667323129477.png



My knowledge of VBA is quite basic, and I can only make a normal copy paste as below. Could any genious please help me with the correct and more complex VBA to achieve it?

>>> use code - tags <<<
Code: 
Sub valuepaste()
Worksheets("AAPL").Range("Table_0").Copy
Worksheets("Sheet1").Range("A2").PasteSpecial Paste:=xlPasteValues
End Sub

Infinite thanks
 

Attachments

Last edited by a moderator:
Hello, according to the initial post attachment an easy Excel basics VBA demonstration as a beginner starter :​
Code: 
Sub Demo1()
    Dim Rw As Range, V
    Set Rw = Sheet2.ListObjects(1).DataBodyRange.Rows
With Sheet1.Cells(Rows.Count, 2).End(xlUp)
    V = Application.Match(.Value2, Rw.Columns(1), 0)
    If IsNumeric(V) Then If V < Rw.Count Then .Cells(2).Resize(Rw.Count - V, Rw.Columns.Count).Formula = Rw(V + 1 & ":" & Rw.Count).Value
End With
    Set Rw = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Marc L said:
Hello, according to the initial post attachment an easy Excel basics VBA demonstration as a beginner starter​
Click to expand...
​
Thank you so much! This works :D I'm super excited. I only have one follow up question. I need to do this procedure with a couple of companies. I was thinking of having a sheet with a Power Query for each company and another sheet with the dowloaded history and the macro to complete it for each too. Here in the code I'm not sure how to reference each worksheet to take the info and where to paste it. Do I just have to change "Sheet2.ListObjects(1).DataBodyRange.Rows" for the next recepient eg. 4 (the 3rd being the next Power Query) and "Sheet1.Cells(Rows.Count, 2).End(xlUp)" by 3? So it would be Sheet4.ListObjects(1).DataBodyRange.Rows and Sheet3.Cells(Rows.Count, 2).End(xlUp)?

Again many many thanks
 
Last edited by a moderator:
You should first correct the PowerQuery import in order numbers are not texts ! Just indicating the decimal separator …​
I used the Formula trick to bypass such bad import.​
​
My VBA demonstration refers to the worksheets codenames - as Sheet2 is the "Table 0" worksheet codename - but​
you can change them with any valid worksheet reference if you prefer to use whatever the worksheet index or name …​
​
 
Thank you so much again, this is fantastic! I could refer to other sheets and use it without problems you're amazing :) Regarding the formatting that is most likely because I'm sitting in EU, for me decimals are with dots and excel interprets it as numbers. I wish you an amazing week!
 
CuriousFox
Did You used that my sent file and press [ Add ] -button?
... or did You something else?
I tested that file ... it runs as it should run ... with Mac.
 
vletm said:
CuriousFox
Did You used that my sent file and press [ Add ] -button?
... or did You something else?
I tested that file ... it runs as it should run ... with Mac.
Click to expand...
Hi vletm! I couldn't use your file since Microsoft blocked the macro's in the file due to the source. However I did copy your code and tested it in my version and sadly it ran into an error. I did use the code that Marc L shared here above without issues. Thank you for your help!
 
CuriousFox
...hmm?
What the source? ... from Sheet0 ... hmm?
Did Your copy have same name for sheets?
Eg if nothing pasted then there were nothing to paste.
... and that error gives an image that there were different sheet names.
Could You send Your version for me, that I could check myself - what is going on?
 
You must log in or register to reply here.
Back
Top