CODEWITHSUNDEEP
excelvba
gatachompp
gatachompp

Reputation: 15

How to copy paste values within a VBA Loop?

I am attempting to copy paste cell values for the contents of cells e2 and f2 back into those same respective cells as part of a looping process, that repeats this task row after row (next row would be the same process for e3 and f3, and then e4 and f4, etc., until a blank cell in column B is reached.

Here is a subset of my code that I can't figure out (it returns an error 1004, PasteSpecial method of Range class failed:

        tickersSheet.Range("E2").Offset(counter, 0).Copy
        tickersSheet.Range("E2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue
        
        tickersSheet.Range("F2").Offset(counter, 0).Copy
        tickersSheet.Range("F2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue

Here is the full code:

Public Sub Email()

    Application.Goto Reference:="Email"

    ' Set a reference to tickers sheet
    Dim tickersSheet As Worksheet
    Set tickersSheet = ThisWorkbook.Worksheets("Tickers")

    ' Set a reference to tickers range
    Dim tickersRange As Range
    Set tickersRange = tickersSheet.Range("B2:B502")

    ' Set a reference to moves sheet
    Dim movesSheet As Worksheet
    Set movesSheet = ThisWorkbook.Worksheets("Moves")
    
    ' Set a reference to moves starting cell
    Dim movesFirstCell As Range
    Set movesFirstCell = movesSheet.Range("C4")
    
    ' Use a counter to make everything else relative
    Dim counter As Long
    counter = 0
    
    ' Loop through cells in tickers sheet starting in B2
    Dim tickersCell As Range
    For Each tickersCell In tickersRange
    
        If tickersCell.Value <> vbNullString Then
            
            
            ' Set row reference
            Dim rowReference As Long
            rowReference = (-2 + counter)
            
            ' Store formula
            movesFirstCell.Formula2R1C1 = "=Tickers!R[" & rowReference & "]C[-1]"
            
            ' Wait for table to generate
            Application.Wait (Now + TimeValue("0:00:10"))
            
            ' Copy range as picture
            movesSheet.Range("B2:L129").CopyPicture
            tickersSheet.Range("H2").Offset(counter, 0).PasteSpecial
            
            ' Copy paste values for correlations
            tickersSheet.Range("E2").Offset(counter, 0).Copy
            tickersSheet.Range("E2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue
            
            tickersSheet.Range("F2").Offset(counter, 0).Copy
            tickersSheet.Range("F2").Offset(counter, 0).PasteSpecial Paste:=x1PasteValue
            
            ' Increment counter
            counter = counter + 1
        
        End If
    
    Next tickersCell

End Sub

Upvotes: 1

Views: 1361

Answers (1)

Ricardo Diaz
Ricardo Diaz

Reputation: 5696

No need to use copy and paste

Just refering to cell value should be enough

Try this:

tickersSheet.Range("F2").Offset(counter, 0).Value = tickersSheet.Range("F2").Offset(counter, 0).Value

Upvotes: 1

Related Questions