Offset issue moving columns from one WS to another-CodePudding

I'm simply trying to move some information from a column to another column on another sheet and using offset to get it to start on the row i need it to start on. trying to not use .copy or .cut or any of the sort.

with ws
    ecol = .cells(7,.columns,count).end(xltoleft).column
    .columns(ecol) = ws2.column("AB").Value2
end with

this works. just places it in the wrong area. so trying to use offset in anyway i'm getting 1004 error

   with ws
        ecol = .cells(7, .columns.count).end(xltoleft).column
        .columns(ecol).offset(7,0) = ws2.column("AB").Value2
    end with

Error 1004

   with ws
        ecol = .cells(7, .columns.count).end(xltoleft).column
        .columns(ecol).offset(7) = ws2.column("AB").Value2
    end with

Error 1004

   with ws
        ecol = .cells(7, .columns.count).end(xltoleft).column
        .columns(7, ecol) = ws2.column("AB").Value2
    end with

i suspect maybe it's because it's pulling the entire AB column from row 1 to 1mm and i just can't change the range.

either way i'd like some help tweaking this. i'm working with a dynamic range on ws2. i thought maybe i could use .end(xlup).row to find the bottom row and adjust the syntax but still getting the same error or i just don't know how to write it.

any help is appreciated.

CodePudding user response：

 with ws
        ecol = .cells(7, .columns.count).end(xltoleft).column   1
        endC = ws.cells(ws.rows.count,"AB).end(xlup).row
        .Range(.Cells(7, ecol), .Cells(7   endC - 1, ecol)) = ws2.Range("AB1:AB" & endC).Value
    end with

CodePudding user response：

I think you were right about the whole column transfer. This code transfers as much of the column as possible.

With ws
        ecol = .Cells(7, .Columns.Count).End(xlToLeft).Column   1
        endRow = .Rows.Count
        .Range(.Cells(7, ecol), .Cells(endRow, ecol)) = ws2.Range("AB1:AB" & endRow - 6).Value
End With

you may want to restrict this to only transferring where there is data but thats up to you!

hope it helps