How to copy paste values within a VBA Loop?

Question

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

So you have formulas in those cells and you want to turn them into values? — Ricardo Diaz
– Ricardo Diaz, Commented Jan 18, 2021 at 22:22

Ricardo Diaz · Accepted Answer · 2021-01-18 22:26:03Z

1

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

answered Jan 18, 2021 at 22:26

Ricardo Diaz

5,7262 gold badges24 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to copy paste values within a VBA Loop?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related