How to copy between sheets In vba, I tried to record macro but it doesn't work

Question

Hello I have this program that will get data from yahoo finance in one sheet then choose the numerical data and then paste it in the my first sheet under the ticker.

Sub fundamentals()

For i = 2 To Sheets(1).Cells(1, 1).End(xlToRight).Column

ticker = Sheets(1).Cells(1, i)

qurl = "https://finance.yahoo.com/quote/" & ticker & "/key-statistics?p=" & ticker & ""

Sheets(2).Select
Sheets(2).Cells.Clear

With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,       Destination:=Sheets(2).Range("A1"))
.BackgroundQuery = True
.Refresh BackgroundQuery = False
End With


Sheets(2).Range("B1:B67").Copy
Sheets(1).Select
Cells(2, i).Select
ActiveSheet.Paste
CutCopyMode = False

Next i

End Sub

It works fine but until it should paste it self to my first sheet, it does not work but in the left corner it says press enter to select destination or paste. I want to make it work automatically so it paste by itself.

Kind regards

I changed the way in which I copy between the sheets. I recorded a macro just copying and used it instead, won't work same thing happen. — Edward, Jun 27 '18 at 09:11
Refrain from using .select/.copy/.paste and just refer to cell values if you need to simply have the value in another cell like so: Cells(1, 2).value = Cells(1, 1).value — JvdV, Jun 27 '18 at 09:20

score 0 · Answer 1 · answered Jun 27 '18 at 09:35

There are multiple things which are not inheritly wrong, but a bad coding practice in your code.

You should avoid using Select, ActiveSheet, Active... like a plague as it comes with a "nice bundle of unforeseen issues which will end up hurting your program in a long run". I would definitely recommend reading this question here, before even doing any further coding yourself:

Also, utitilize the procedures. Don't just dump everything into huge chunks of code. Your future self will thank you later for readability (or anyone who tries to update the code after you, even more).

I'm not exactly sure, if what I'm about to do is right, as it's not exactly clear from your original question, what the expected result would be, but this should serve as at least as some form of template of what your code should look like:

Private Sub update_table()

Sheets(2).Cells.Clear

Dim qurl As String
Dim ticker As Long

For i = 2 To Sheets(1).Cells(1, 1).End(xlToRight).Column
    ticker = Sheets(1).Cells(1, i)
    qurl = "https://finance.yahoo.com/quote/" & ticker & "/key-statistics?p=" & ticker & ""

    With Sheets(2).QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets(2).Range("A1"))
        .BackgroundQuery = True
        .Refresh BackgroundQuery = False
    End With
Next i

End Sub

and the copy_paste which invokes update_table()

Private Sub cp_table()

    Call update_table
    Sheets(2).Range("B1").CurrentRegion.Copy
    Sheets(1).Range("A2").PasteSpecial

End Sub

If you can update your question with expected input data and expected output, I can edit my answer accordingly to make sure the code works and doesn't only serve as a pseudo-code.

How to copy between sheets In vba, I tried to record macro but it doesn't work

1 Answers1