VBA Excel Cut search paste not exact cell address

[Rowland Hamilton]

Trying to move the "Misc. Rev" Column to the right of the "Veratax" column. What am I doing wrong? I don't want to specify the exact cells because the position of these columns can change. But, all my attempts to copy, go to another part of the sheet through searches and arrows, then paste in the relative spot I arrive in aren't working. Only when I select an exact cell between copy and paste have I been able to get this to work. The code thus far:

Sub test_cut_move()
'Move Misc. Rev to end, delete check column
    Rows("6:6").Select
    Selection.Find(What:="Misc Rev", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.Cut
    Rows("6:6").Select
    Selection.Find(What:="veratax", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Select
    Selection.End(xlUp).Select
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Select
    Selection.EntireColumn.Delete
End Sub

Thank you, Rowland

 

[JackDanIce]

Try on some dummy data first, suggest you use F8 to step through it to see what it does:

Sub test_cut_move_v2()
Dim mySource As Range, myDestination As Range
Set mySource = Rows("6:6").Find(What:="Misc Rev").EntireColumn
Set myDestination = Rows("6:6").Find(What:="veratax").Offset(0,1).EntireColumn
mySource.Cut myDestination
myDestination.Offset(0,1).EntireColumn.Delete
End Sub

 

[Rowland Hamilton]

I figured it out by recording the macro, I needed to use:

 Selection.Insert Shift:=xlToRight

If you know another reason why it didn't work, let me know.
Solution:

Sub Copy_Search_Paste_Column_Test2()
Rows("6:6").Select
    Selection.Find(What:="Misc Rev", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireColumn.Select
    Selection.Cut
    
   Rows("6:6").Select
    Selection.Find(What:="veratax", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        ActiveCell.Offset(-5, 1).Select
        Selection.Insert Shift:=xlToRight
        Range("b6").Select
   
End Sub

Thank you, Rowland

 

[JackDanIce]

I think this modification will work:

Sub test_cut_move_v2()
Dim mySource As Range, myDestination As Range
Set mySource = Rows("6:6").Find(What:="Misc Rev").EntireColumn
Set myDestination = Rows("6:6").Find(What:="veratax").Offset(0,1).EntireColumn.Insert Shift:=xlToRight
mySource.Cut myDestination
Range("B6").select
End Sub

 

[Rowland Hamilton]

Thanks, JackDanIce.

I've been recording a macro while writing code, then stepping through with F8 the whole time. Sometimes I turned off the recorder for fear it was interfering with the macro operation. Saw your response after my eureka moment.

Rowland

 

[JackDanIce]

No probs, hope it helped.

 

[Rowland Hamilton]

JackDanIce:

I knew my destination was not being locked in before because the "select destination" dialogue box never closed before I got my runtime error with the macro. I'll assume your solution clearly makes it recognize the destination. I'll use mine for now and keep your's in my back pocket.

Thanks, Rowland

 

[JackDanIce]

It might be an idea to try mine to see how it differs from yours and if it helps with any future coding. One thing I've been taught is to minimize the amount of ".select" you have in your code, makes it faster, more efficient and less likely to mess up! Good luck anyway