0

I need to move group codes out of their current column and move to the left 1 column. this is in a Macro because the rows will change based on the file/tables. I've found an answer that seems as simple as the idea, but its not working for me. The columns don't really change.

'Moving Group Codes

Dim cell As Range
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
    If Len(cell.Value) = 3 Then
        'Offset Paste (offsets 0 cells down and 1 to the LEFT)
    Selection.Copy
    Selection.Offset(, -1).Select
    ActiveSheet.Paste
    End If
Next cell

Error: Run-time error '438' Object doesn't support this property or method

Code getting error:

Selection.Offset(, -1).Select

Any help will be greatly appreciated. Thank you, Julie

I tried these codes: all fails

Selection.ColumnOffset(, -1).Paste

Selection.ColumnOffset(, -1).Select
ActiveSheet.Paste

I'm sure I tried more, but they all failed.

Tom Brunberg
  • 20,312
  • 8
  • 37
  • 54
Julie
  • 15
  • 3
  • re-pasting code to hopefully make it easier to read: – Julie Jun 18 '23 at 14:55
  • 1
    Seems like the issue is that you are referring to `Selection` instead of `cell`. – DecimalTurn Jun 18 '23 at 15:48
  • 1
    To copy the values to the column adjacent to the left, in the *IF* statement, you could use the simple `cell.Offset(, -1).Value = cell.Value`. If you don't want to use offset but a particular column instead (column `B` in this case), you could use `cell.EntireRow.Columns("B").Value = cell.Value` instead. – VBasic2008 Jun 18 '23 at 15:50
  • I recommend reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424) and [How to avoid copy/paste](https://stackoverflow.com/a/64611707/16578424) - if you apply those adivses, the error will most likely be gone. – Ike Jun 19 '23 at 12:30
  • Thank you so much for your feed back. I will read those. I did come back at this morning and must have had a clearer head. I was able to work through and get the results I wanted. YAY!! I think you are right about the 'cell' part of the code. :) code: Dim cell As Range For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange) If Len(cell.Value) = 3 Then cell.Cut Destination:=cell.Offset(columnoffset:=-1) End If Next cell – Julie Jun 19 '23 at 15:07

1 Answers1

0

This morning I took another look and found a solution that worked like a CHARM!

Dim cell As Range
For Each cell In Intersect(Range("C:C"), ActiveSheet.UsedRange)
    If Len(cell.Value) = 3 Then
    cell.Cut Destination:=cell.Offset(columnoffset:=-1)
    End If
Next cell

I couldn't be Happier with the results and simplicity of the code.

Julie
  • 15
  • 3