VBA code to copy cells in specific column and paste (value) it to cells in specific column on another Sheet based on conditions

Question

VBA noob here, I need to copy from specific column in Sheet1 and pastevalue it to specific column in Sheet2 based on 2 conditions. Details (it's not a code):

i=2 & j=4

IF Sheet1.Cells(i, 4)<>0 and Sheet1.Cells(i, 49)<>0, Type "FK" in _
Sheet2.Cells(j, 1), Type "OF" in Sheet2.Cells(j + 1, 1), copy the following

Copy from **Sheet1**      Paste in **Sheet2**
Cells(i, 52)              Cells(j, 2)
Cells(i, 51)              Cells(j, 3)
Cells(i, 4)               Cells(j, 4)
Cells(i, 1)               Cells(j, 5)
Cells(i, 53)              Cells(j, 6)
Cells(i, 7)               Cells(j, 7)
Cells(i, 10)              Cells(j, 8)
Cells(i, 5)               Cells(j, 9)
Cells(i, 6)               Cells(j, 10)
Cells(i, 49)              Cells(j, 11)
Cells(i, 50)              Cells(j, 12)
Cells(i, 51)              Cells(j, 13)
Cells(i, 51)              Cells(j, 15)
Cells(i, 51)              Cells(j, 16)
Cells(i, 51)              Cells(j, 17)
Cells(i, 19)              Cells(j + 1, 2)
Cells(i, 20)              Cells(j + 1, 3)
Cells(i, 44)              Cells(j + 1, 4)
Cells(i, 28)              Cells(j + 1, 5)
Cells(i, 30)              Cells(j + 1, 6)
Cells(i, 37)              Cells(j + 1, 7)
Cells(i, 41)              Cells(j + 1, 8)
Cells(i, 46)              Cells(j + 1, 9)
Cells(i, 51)              Cells(j + 1, 10)
Cells(i, 51)              Cells(j + 1, 11)

next i

IF Sheet1.Cells(i, 4)<>0 and Sheet1.Cells(i, 49)=0, Type "OF" in _
Sheet2.Cells(j, 1), copy the following

Copy from **Sheet1**    Paste in **Sheet2**
Cells(i, 19)            Cells(j, 2)
Cells(i, 20)            Cells(j, 3)
Cells(i, 44)            Cells(j, 4)
Cells(i, 28)            Cells(j, 5)
Cells(i, 30)            Cells(j, 6)
Cells(i, 37)            Cells(j, 7)
Cells(i, 41)            Cells(j, 8)
Cells(i, 46)            Cells(j, 9)
Cells(i, 51)            Cells(j, 10)
Cells(i, 51)            Cells(j, 11)

next i

Else, next i (start again for i + 1)

So, basically what I'm trying to do is, if condition 1 is true I will copy data from 1 row and paste it into the next 2 empty rows in another sheet. Meanwhile, if condition 2 is true, copy data from 1 row and paste it into the next empty row in another sheet. Sorry for the long post, don't know any other way to explain it. Thank you all before.

You code or explanation doing things row based not column based? — ali srn

J. Kamans J. Kamans · Accepted Answer · 2016-09-11T00:56:07

Is this doing what you want:

    Sub copyABunchOfThings()

    Dim i As Integer, j As Integer
    i = 2
    j = 4

    For i = 1 To 10 'Change 1 and 10 into your numbers.

    If Sheet1.Cells(i, 4) <> 0 And Sheet1.Cells(i, 49) <> 0 Then
        Sheet2.Cells(j, 1).Value = "FK"
        Sheet2.Cells(j + 1, 1).Value = "OF"

        'Put in **Sheet 2**   what is in **Sheet 1**
        Sheet2.Cells(j, 2) = Sheet1.Cells(i, 52)
        Sheet2.Cells(j, 3) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 4) = Sheet1.Cells(i, 4)
        Sheet2.Cells(j, 5) = Sheet1.Cells(i, 1)
        Sheet2.Cells(j, 6) = Sheet1.Cells(i, 53)
        Sheet2.Cells(j, 7) = Sheet1.Cells(i, 7)
        Sheet2.Cells(j, 8) = Sheet1.Cells(i, 10)
        Sheet2.Cells(j, 9) = Sheet1.Cells(i, 5)
        Sheet2.Cells(j, 10) = Sheet1.Cells(i, 6)
        Sheet2.Cells(j, 11) = Sheet1.Cells(i, 49)
        Sheet2.Cells(j, 12) = Sheet1.Cells(i, 50)
        Sheet2.Cells(j, 13) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 15) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 16) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 17) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j + 1, 2) = Sheet1.Cells(i, 19)
        Sheet2.Cells(j + 1, 3) = Sheet1.Cells(i, 20)
        Sheet2.Cells(j + 1, 4) = Sheet1.Cells(i, 44)
        Sheet2.Cells(j + 1, 5) = Sheet1.Cells(i, 28)
        Sheet2.Cells(j + 1, 6) = Sheet1.Cells(i, 30)
        Sheet2.Cells(j + 1, 7) = Sheet1.Cells(i, 37)
        Sheet2.Cells(j + 1, 8) = Sheet1.Cells(i, 41)
        Sheet2.Cells(j + 1, 9) = Sheet1.Cells(i, 46)
        Sheet2.Cells(j + 1, 10) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j + 1, 11) = Sheet1.Cells(i, 51)

    ElseIf Sheet1.Cells(i, 4) <> 0 And Sheet1.Cells(i, 49) = 0 Then
        Sheet2.Cells(j, 1) = "OF"

        'Put in **Sheet 2**   what is in **Sheet 1**
        Sheet2.Cells(j, 2) = Sheet1.Cells(i, 19)
        Sheet2.Cells(j, 3) = Sheet1.Cells(i, 20)
        Sheet2.Cells(j, 4) = Sheet1.Cells(i, 44)
        Sheet2.Cells(j, 5) = Sheet1.Cells(i, 28)
        Sheet2.Cells(j, 6) = Sheet1.Cells(i, 30)
        Sheet2.Cells(j, 7) = Sheet1.Cells(i, 37)
        Sheet2.Cells(j, 8) = Sheet1.Cells(i, 41)
        Sheet2.Cells(j, 9) = Sheet1.Cells(i, 46)
        Sheet2.Cells(j, 10) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 11) = Sheet1.Cells(i, 51)
    End If

    Next i

    End Sub

VBA code to copy cells in specific column and paste (value) it to cells in specific column on another Sheet based on conditions

1 Answers