VBA How to paste rows several columns over?

Question

This currently my code, based off of this answer: https://stackoverflow.com/a/11633207.

My code currently pastes the copied code on the bottom of Sheet2 starting at column A. How can I make it so that it copies the row starting at column C?

Dim ws1 As Worksheet, ws2 As Worksheet
Dim copyFrom As Range
Dim lRow As Long
Dim lastRow As Long
Dim strSearch As String
Dim i As Integer

Set ws1 = Worksheets("Sheet1")

With ws1
    .AutoFilterMode = False
    lRow = .Range("J" & .Rows.Count).End(xlUp).Row

    With .Range("J1:J" & lRow)
        strSearch = "John"
        .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
        Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
    End With


    Set ws2 = Worksheets("Sheet2")
    With ws2
        lastRow = ws2.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
        copyFrom.Copy .Rows(lastRow + 1)

    End With

    .AutoFilterMode = False
End With

note: `.Offset(1, 0)` is same as `.Offset(1)` ... means: next row down — jsotola, Oct 17 '17 at 18:48

Ibo · Accepted Answer · 2017-10-17T22:10:15.383

1

You can write this code a lot better and there is no need to copy the entire row and paste it. This will limit you, however just to easily solve your problem replace this line:

copyFrom.Copy .Rows(lastRow + 1)

with this:

 Set Rng = copyFrom.SpecialCells(xlCellTypeConstants)
 Rng.Copy .Cells(lastRow + 1, 3)

Note that 3 represents column C and you can actually change it to whatever column you want.

edited Oct 17 '17 at 22:10

answered Oct 17 '17 at 18:38

Ibo

4,081
6
45
65

you can use `.Cells(lastRow + 1, "C")` – jsotola Oct 17 '17 at 18:41
Perfect this is exactly what I was looking for. Thanks! – ac1243 Oct 17 '17 at 19:59

Excelosaurus · Answer 2 · 2017-10-17T19:20:57.683

0

A quick way to do this is to still copy/paste the entire rows, but to then delete the first 2 columns on the destination side's rows, shifting towards the left:

copyFrom.Copy .Rows(lastRow + 1) 'As before.

'Edit:

'If you meant to pull the pasted data towards the left:
.Range(.Cells(lastRow + 1, 1), .Cells(lastRow + GetRowsInRange(copyFrom), 2)).Delete Shift:=XlDeleteShiftDirection.xlShiftToLeft

'If you meant to push the pasted data towards the right:
.Range(.Cells(lastRow + 1, 1), .Cells(lastRow + GetRowsInRange(copyFrom), 2)).Insert Shift:=XlInsertShiftDirection.xlShiftToRight

Edit: you have to compute the number of rows that were copied.

Public Function GetNumRowsInRange(ByVal prngFullRows As Excel.Range) As Long
    Dim result As Long
    Dim rngArea As Excel.Range

    For Each rngArea In prngFullRows.Areas
        result = result + rngArea.Rows.Count
    Next

    GetNumRowsInRange = result
End Function

edited Oct 17 '17 at 19:20

answered Oct 17 '17 at 18:09

Excelosaurus

2,789
1
14
20

With all due respect, you are making the asker confused, this is not how VBA works, we don't mimic the User Interface manipulations in VBA and there is no need for him to count the copied rows – Ibo Oct 17 '17 at 18:51
have you tested that your solution works really? You are deleting a range and shifting to the left, how could it solve the problem for him? – Ibo Oct 17 '17 at 18:52
Counting rows: because he's only copying the visible cells' entire rows, which could be a multi-area range. Shifting to the left: yeah, I'm probably wrong; he wants to push the results towards the right instead: .Insert Shift:=XlInsertShiftDirection.xlShiftToRight. My idea was to induce minimal code changes but hell's paved with good intentions. – Excelosaurus Oct 17 '17 at 19:08

score -1 · Answer 3 · answered Oct 17 '17 at 18:03

-1

Set offset to move to 3 column which is C:

Set copyFrom = .Offset(1, 3).SpecialCells(xlCellTypeVisible).EntireRow

Please try if this works for you,

answered Oct 17 '17 at 18:03

maaajo

839
6
10

copyFrom will still refer to the entire row. – Excelosaurus Oct 17 '17 at 18:10
you cannot use offset for entirerow ranges – Ibo Oct 17 '17 at 18:26
He's not offsetting an entire row; the startpoint is a range in column J. – Excelosaurus Oct 17 '17 at 18:36
@Excelosaurus, do this in an immediate window `Range("j2").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Select`. you will see that the range starting point is at A1 – jsotola Oct 17 '17 at 18:46
copyFrom is an `EntireRow` range, how could it start from column J? – Ibo Oct 17 '17 at 18:50
My first comment holds: the answer OP's copyFrom will still refer to entire rows while the question's OP wants to copy starting at column C. My second comment was to say that @maaajo's use of Offset is legal (but of course will ultimately yield entire rows). By startpoint, I was thinking about the With Block variable. My answer copies entire rows but chops them off after the fact. – Excelosaurus Oct 17 '17 at 18:58

VBA How to paste rows several columns over?

3 Answers3