How to copy paste values within a VBA Loop?

gatachompp

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
Ricardo Diaz

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

VBA COPY AND PASTE IN A LOOP

How can I skip non-numeric values when copy and paste using loop in VBA?

Copy rows between two values VBA and paste to new sheet loop

Loop through a workbook and copy & paste values in to another workbook (VBA, Excel)

VBA - Very slow loop through rows to copy/paste values

VBA copy-paste loop

Filter and Copy Paste the Values VBA

Copy paste as values Excel VBA

How to copy paste all formats AND values without using select in vba

VBA paste as values - how to

Using offset within a loop to copy and paste

VBA Copy & Paste Loop ( Generate Field Number)

Copy paste loop vba - overwrites the current cell

VBA Loop : Copy / Paste destination offsetting

Excel VBA Copy Paste Values only

VBA Copy range values and paste in another sheet

VBA copy and paste pivot values and format

Excel VBA: filter list for "empty" (failed matching) values then copy and paste to new column Code loop

Excel VBA - Copy and Paste Loop in VBA based on cell value

Using set() from data.table package to copy and paste values from a data frame to another, within a loop of data frame creation

How do i copy and paste data to worksheets that i created in VBA using for loop?

How to copy a range and paste diagonally using vba

VBA - How to copy a column once but paste it twice?

How to copy/paste a table in footer word vba

Copy / paste within for cycle

How to copy the values in a range from one sheet to and paste it another sheet on a predefied order using VBA?

How to copy/paste Values after using VLookup

Excel - VBA Loop - Copy formulas from cells, paste into cells, then copy paste special

VBA Loop to copy and paste data from nth column to another workbook