VBA Cutting/Pasting from one column to another depending on variable

[firesale]

Good Evening, I'm still fairly new to VBA and my googling hasn't gotten me results. I'm trying to move data from 3 columns to a different 3 columns, same workbook and sheet, even the same row. The column data is being moved to depends on what is in the first column, from what I can tell this requires a Case statement. I've entered 3 of the values in my code so far and when I run the macro nothing actually happens. See code below.

Sub MoveTax()


Select Case Column1
Case "CA1"
Range("A:C").Cut Range("D:F")


Case "PA12"
Range("A:C").Cut Range("G:I")


Case "PA10"
Range("A:C").Cut Range("G:I")
End Select


End Sub

This is the desired outcome:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[TD]Column 5[/TD]
[TD]Column 6[/TD]
[TD]Column 7[/TD]
[TD]Column 8[/TD]
[TD]Column 9[/TD]
[/TR]
[TR]
[TD]CA1[/TD]
[TD]$3[/TD]
[TD]$8[/TD]
[TD]here[/TD]
[TD]here[/TD]
[TD]here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CA1[/TD]
[TD]$5[/TD]
[TD]$7[/TD]
[TD]here[/TD]
[TD]here[/TD]
[TD]here[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PA10[/TD]
[TD]$8[/TD]
[TD]$9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]here[/TD]
[TD]here[/TD]
[TD]here[/TD]
[/TR]
[TR]
[TD]PA12[/TD]
[TD]$13[/TD]
[TD]$19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]here[/TD]
[TD]here[/TD]
[TD]here[/TD]
[/TR]
</tbody>[/TABLE]










This is where I was getting my information from and I'm sure it's lacking context that I'm not able to determine: https://www.techonthenet.com/excel/formulas/case.php

 

[footoo]

Dim rng As Range, cel As Range
Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
For Each cel In rng
    Select Case cel
        Case "CA1": cel.Resize(, 3).Cut cel(1, 4)
        Case "PA10", "PA12": cel.Resize(, 3).Cut cel(1, 7)
    End Select
Next

 

[firesale]

Dim rng As Range, cel As Range
Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(3).Row)
For Each cel In rng
    Select Case cel
        Case "CA1": cel.Resize(, 3).Cut cel(1, 4)
        Case "PA10", "PA12": cel.Resize(, 3).Cut cel(1, 7)
    End Select
Next

You're awesome, this does what I need. Can you by chance explain to me what is going on with each Case statement and why they are written different? Reason I ask is I have up to 61 of these codes that will need shuffled in to columns depending on which code it is, so PA10 and 12 would go in those columns but PA15 would go in a different set of 3. I was hoping to replicate this script with the others and essentially update the "CA/PA/1/10/12" part of the string.

From what I'm understanding in the CA1 statement it's cutting 3 cells beginning with column 1 and pasting them in to 3 cells beginning with column 4? If my understanding of this is correct then I shouldn't have any problem duplicating your code. Again, thank you for your help!

 

[footoo]

Your understanding of the code is correct.
The Case statements could also be written like this :

Case "CA1": cel.Resize(, 3).Cut cel.Offset(0, 3)
Case "PA10", "PA12": cel.Resize(, 3).Cut cel.Offset(0, 6)

It is not necessary to put PA10 and PA12 on separate lines since the action based these values is the same.

 

[firesale]

The .Offset code actually worked better for my needs. You saved my bacon. Thank you!