CODEWITHSUNDEEP
excelvba
Indra Royal Silver
Indra Royal Silver

Reputation: 23

copy cell values

My data looks like

enter image description here

Sub CopyCols()
    Dim lngRow As Long, lngRow As Long

    lngRow = 1

    With ActiveSheet
            '.Cells(1, "E").Resize(.UsedRange.Rows.Count, 3).Copy .Cells(1, "A")
            .Cells(lngRow, "A").Value = .Cells(lngRow, "E").Value
            .Cells(lngRow, "B").Value = .Cells(lngRow, "F").Value
            .Cells(lngRow, "C").Value = .Cells(lngRow, "G").Value
            lngRow = lngRow + 1
        Next lngRow
    End With 'ActiveSheet
End Sub

I am trying to copy E1:G16 to A1:C16 but it is not working? Can anyone can help with that?

Upvotes: 0

Views: 66

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Your code has error. You are not looping correctly. In fact I do not see a loop just a Next without For

Your code can be written as

Dim ws As Worksheet
Dim i As Long

'~~> Change this to the relevant sheet
Set ws = Sheet1

With ws
    For i = 1 To 16
        .Cells(i, "A").Value = .Cells(i, "E").Value
        .Cells(i, "B").Value = .Cells(i, "F").Value
        .Cells(i, "C").Value = .Cells(i, "G").Value
    Next i
End With

However there is no need for a loop. You can do it in one line of code

Dim ws As Worksheet

'~~> Change this to the relevant sheet
Set ws = Sheet1

With ws
    .Range("A1:C16").Value = .Range("E1:G16").Value
End With

Upvotes: 3

Related Questions