CODEWITHSUNDEEP
vbaexcelloops
JordanCA57
JordanCA57

Reputation: 125

Copy and Loop using dynamic column number

I'm trying to use "j" as a counter for columns. I tried using it as follows, but in what I have "j" is representing a row. The macro works, but only copies row 25 - 27.

Can anyone tell me how to use this with j as the column number? Also, how would I copy this so that the column width is also retained?

    Sub CopyFinal()


        Set i = Sheets("MedicalBenefits")
        Set e = Sheets("Final")

        Dim j As Integer
        j = 2

        Application.ScreenUpdating = False

        Do Until IsEmpty(i.Cells(5, j))
            i.Range(j & "5:" & j & "27").Copy e.Range(j & "5:" & j & "27")
            j = j + 1
        Loop

        Application.ScreenUpdating = True

End Sub

Upvotes: 0

Views: 356

Answers (2)

iDevlop
iDevlop

Reputation: 25272

Use Offset()

    j = 0

    Application.ScreenUpdating = False

    Do Until IsEmpty(range("B5").offset(0,j))
        i.Range("B5:B27").Offset(0,j).Copy e.Range("B5:B27").Offset(0,j)
        j = j + 1
    Loop

Upvotes: 0

user3598756
user3598756

Reputation: 29421

switch to Cells(rowIndex, columnIndex) syntax to use integer columns index

    Do Until IsEmpty(i.Cells(5, j))
        i.Cells(5, j).Resize(23).Copy e.Cells(5, j)
        j = j + 1
    Loop

Upvotes: 1

Related Questions