VBA Offset excludes columns problem

[drluke]

I want to copy columns A - I in a worksheet1 to another worksheet2 also in columns A-I. In my copy from worksheet1 I am finding the last row with referencing column B. The reason for doing that is because column A contains data from rows 2-5000, whereas columns B-I contains data in rows 2 - 400. I only want to copy rows 2-400 including column A (copy A2:I400). I have used "offset" in my code to include column A in the range I want to copy. Whilst column A is being copied, column I is not. I would appreciate any advice to over come this problem.

Private Sub cmdAddProcessYes_Click()

    Dim lrow As Long
    Dim lastR As Long
    
    ThisWorkbook.Activate
        lrow = Worksheets("Additions").Cells(Worksheets("Additions").Rows.Count, "B").End(xlUp).Row
        
        lastR = Worksheets("Test").Cells(Worksheets("Test").Rows.Count, "A").End(xlUp).Row
        lastR = lastR + 1
            
        Worksheets("Additions").Range("B2:I" & lrow).Offset(, -1).Copy
            
        Worksheets("Test").Range("A" & lastR).PasteSpecial
            
        Worksheets("Test").Activate

End Sub

 

[maabadi]

Try this:

Private Sub cmdAddProcessYes_Click()

    Dim lrow As Long, lastR As Long
         lrow = Worksheets("Additions").Cells(Worksheets("Additions").Rows.Count, "B").End(xlUp).Row
        lastR = Worksheets("Test").Cells(Worksheets("Test").Rows.Count, "A").End(xlUp).Row + 1
        Worksheets("Additions").Range("A2:I" & lrow).Copy Worksheets("Test").Range("A" & lastR)
        Worksheets("Test").Activate

End Sub

 

[drluke]

Try this:

Private Sub cmdAddProcessYes_Click()

    Dim lrow As Long, lastR As Long
         lrow = Worksheets("Additions").Cells(Worksheets("Additions").Rows.Count, "B").End(xlUp).Row
        lastR = Worksheets("Test").Cells(Worksheets("Test").Rows.Count, "A").End(xlUp).Row + 1
        Worksheets("Additions").Range("A2:I" & lrow).Copy Worksheets("Test").Range("A" & lastR)
        Worksheets("Test").Activate

End Sub

Thank you so much - exactly what I wanted.

 

[Peter_SSs]

maabadi's code does what you want, but you could also use your existing code with this one minor change

Your code

Worksheets("Additions").Range("B2:I" & lrow).Offset(, -1).Copy

One letter changed

Worksheets("Additions").Range("B2:J" & lrow).Offset(, -1).Copy

 

[maabadi]

You're Welcome & Thanks for follow-up.

 

[drluke]

maabadi's code does what you want, but you could also use your existing code with this one minor change

Your code

Worksheets("Additions").Range("B2:I" & lrow).Offset(, -1).Copy

One letter changed

Worksheets("Additions").Range("B2:J" & lrow).Offset(, -1).Copy

Thank you. Very helpful.

 

[Peter_SSs]

You're welcome. Glad to contribute. Thanks for the follow-up.