CODEWITHSUNDEEP
excelvba
idk
idk

Reputation: 53

VBA macro help needed

I'm currently working on a project involving option chains and I need to copy the value from a single cell to another sheet in the same book every five minutes, and in the same column. However, due to my unfamiliarity with VBA this is the best macro I could make, and it's very inefficient:

Sub Macro1()
    Sheets("Sheet1").Select
    Range("A1").Copy
    Sheets("Sheet2").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteValues
    ActiveCell.Offset(1, 0).Select
    Sheets("Sheet1").Select
End Sub

Note: A1 is a placeholder, and I'm currently dealing with the information pasting, not the 5 minute timer.

How could I improve the macro?

Upvotes: 0

Views: 146

Answers (1)

Harun24hr
Harun24hr

Reputation: 37050

Try-

Sub Macro1()
Dim ws As Worksheet
Dim lrow As Long

    Set ws = Worksheets("Sheet2")
    lrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ws.Range("A" & lrow) = Sheets("Sheet1").Range("A1").Value2
    
    Set ws = Nothing
End Sub

Upvotes: 1

Related Questions