Looped column reference in excel VBA to change for another worksheet

Question

I have VBA code in Excel. I have it looping a column (E2:E100). I want to just copy a cell (the same reference) from one worksheet to another.

Sub et()
Dim c As Range
Dim a As Range


Worksheets("CSV2").Select
'//loop it

For Each c In Range(Range("E2"), Range("E2").End(xlDown))
Set a = Worksheets("CSV").c '// The problem is here. I want the same range selection [c] as worksheet (CSV2) to the worksheet ("CSV") for the range selection [a]. I want to copy directly from [a] to [c]. I want to keep it in this format. Just help with the line here. Thanks.
    
    If c.Value > 0 & c.Value < 3 Then


        Sheets("CSV").Select
        a.Copy'// here I have it to copy the "CSV" sheet
        Sheets("CSV2").Select
        c.PasteSpecial Paste:=xlPasteValues'// here I want to paste it to the "CSV2" sheet
    End If
   
Next
Worksheets("RETURN").Select
End Sub

Ricardo Diaz · Accepted Answer · 2020-12-19T17:44:46.067

You should avoid selecting sheets and cells (read this)

In my code I followed the logic you had about finding the source range through using End(xlDown) but you should consider finding the last cell in a row through a different approach (read this)

Read the code's comments and adjust it to fit your needs

Public Sub CopyValues()
    
    ' Set source sheet
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("CSV")
    
    ' Set target sheet
    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("CSV2")
    
    ' Set source range
    Dim sourceRange As Range
    Set sourceRange = sourceSheet.Range("E2:E" & sourceSheet.Range("E2").End(xlDown).Row)
    
    ' Loop through cells in range
    Dim sourceCell As Range
    For Each sourceCell In sourceRange.Cells
    
        If sourceCell.Value > 0 And sourceCell.Value < 3 Then
            
            ' This is one alternative to paste only values
            targetSheet.Range(sourceCell.Address).Value = sourceCell.Value
            
            ' This is another alternative to paste the cell with value and formats (just comment previous line, and uncomment next to use it)
            'sourceCell.Copy targetSheet.Range(sourceCell.Address)
            
        End If
    
    Next sourcell
    

End Sub

Let me know if it works

I can't get the `sourceSheet.Range("E2:E" & sourceSheet.Range("E2").End(xlDown))` part of the code to work. Is there any other way (simple if possible) to refer to E2 to end of E column. Thanks. — Brian D, Dec 19 '20 at 17:16

Looped column reference in excel VBA to change for another worksheet

1 Answers1