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

firesale

New Member
Joined
Jul 17, 2019
Messages
3
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.

Code:
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:

Column 1Column 2Column 3Column 4Column 5Column 6Column 7Column 8Column 9
CA1$3$8hereherehere
CA1$5$7hereherehere
PA10$8$9hereherehere
PA12$13$19hereherehere

<tbody>
</tbody>










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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,037
Office Version
2016
Platform
Windows
Code:
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

New Member
Joined
Jul 17, 2019
Messages
3
Code:
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

Well-known Member
Joined
Sep 21, 2016
Messages
3,037
Office Version
2016
Platform
Windows
Your understanding of the code is correct.
The Case statements could also be written like this :
Code:
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

New Member
Joined
Jul 17, 2019
Messages
3
The .Offset code actually worked better for my needs. You saved my bacon. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,511
Messages
5,487,321
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top