Move Under Marco not working when altered to move between sheets

Question

I am trying to alter my move under macro to move columns under columns on a different sheet.

It worked fine when I was moving columns under other columns on the same sheet.

I can not get the proper syntact for the wsS.Range(Cells(2, j), Cells(lRow, j)).Copy _ Destination:=wsT.Range(Cells(LR, k), Cells(LR, k)).Offset(1, 0)

When I run the macro nothing happens no errors are thrown and nothing is moved

Thanks

Sub MoveUnder()
Dim wsS As Excel.Worksheet
Dim wsT As Worksheet
Dim ar As Variant
Dim er As Variant
Dim i As Variant
Dim h As Variant
Dim j As Long
Dim k As Long
Dim lRow As Long
Dim LR As Long


Set wsS = ActiveWorkbook.Sheets(1)
Set wsT = ActiveWorkbook.Sheets(2)

    ar = Array("user id", "user name")    ' Find column to copy
    er = Array("user id", "user name")    ' Find column to paste beneith

lRow = wsS.Range("A" & Rows.count).End(xlUp).Row
LR = wsT.Range("A" & Rows.count).End(xlUp).Row

On Error Resume Next
For i = LBound(ar) To UBound(ar)
    j = wsS.Rows(1).Find(ar(i), Rows(1).Cells(Rows(1).Cells.count), , xlWhole,   xlByRows).Column
    k = wsT.Rows(1).Find(er(i), Rows(1).Cells(Rows(1).Cells.count), , xlWhole, xlByRows).Column

      wsS.Range(Cells(2, j), Cells(lRow, j)).Copy _
   Destination:=wsT.Range(Cells(LR, k), Cells(LR, k)).Offset(1, 0)
Next i
On Error GoTo 0

End Sub

Comment out `On Error Resume Next` to see what error is being thrown. — Automate This, Apr 23 '14 at 15:31
Hello Portland Runner, I get `Method Range of object worksheet failed` and `wsS.Range(Cells(2, j), Cells(lRow, j)).Copy _ Destination:=wsT.Range(Cells(LR, k), Cells(LR, k)).Offset(1, 0)` is highlighted which I thought was the problem but I can not get what is wrong, thanks — xyz, Apr 23 '14 at 15:36

score 2 · Accepted Answer · edited May 23 '17 at 12:28

I've slightly modified your code to be more reliable:

Sub MoveUnder()
    Dim wsS As Worksheet, wsT As Worksheet
    Dim ar, er, i, h, j, k
    Dim lRow As Long, LR As Long
    Dim rng1 As Range, rng2 As Range

    Set wsS = ActiveWorkbook.Sheets(1)
    Set wsT = ActiveWorkbook.Sheets(2)

    ar = Array("user id", "user name")    ' Find column to copy
    er = Array("user id", "user name")    ' Find column to paste beneith

    lRow = wsS.Range("A" & wsS.Rows.Count).End(xlUp).Row
    LR = wsT.Range("A" & wsT.Rows.Count).End(xlUp).Row

    For i = LBound(ar) To UBound(ar)
        Set rng1 = wsS.Range("1:1").Find(ar(i), wsS.Cells(1, wsS.Columns.Count), , xlWhole, xlByRows)
        Set rng2 = wsT.Range("1:1").Find(er(i), wsT.Cells(1, wsT.Columns.Count), , xlWhole, xlByRows)
        If Not rng1 Is Nothing And Not rng2 Is Nothing Then
            j = rng1.Column
            k = rng2.Column

            wsS.Range(wsS.Cells(2, j), wsS.Cells(lRow, j)).Copy _
                Destination:=wsT.Cells(LR + 1, k)
        End If
    Next i
End Sub

You should fully qualify your Cells objects like this: wsT.Cells(LR, k) and so on.

Also check this link for explanation Why I should use On Error Resume Next judiciously

Thank you, that worked perfectly. simoco I notice that you have `Dim ar, er, i, h, j, k` these do not need specific Dims like `Dim ar As Variant` ? — xyz, Apr 23 '14 at 15:45
By default all variables has type `Variant` untill you specify any other, so you can always ommit `As Variant` part in your declaration — Dmitry Pavliv, Apr 23 '14 at 15:47

Move Under Marco not working when altered to move between sheets

1 Answers1