Shift select data

[JLandrum]

Good afternoon everyone, need some help. Im trying to move THE DATA from each column listed from 1, 2, 3, 4, to 11, 22, 33, 44 in VBA. There's other data but not relevant to what I need. So, whatever data that is inputted into 1,2,3,4 it will automatically move that data to 11,22,33,44. Then it will need to be sorted by column 11,44,33. Any and all help is greatly appreciated, thank you

[mperrah]

Based on your test page the columns are 3, 4, 6 and 7
moving to 14, 15, 12 and 13 respectively
not elegant but functional:

Sub vbax52603()

lr = Cells(Rows.Count, 1).End(xlUp).Row
    Range("C3:C" & lr).Copy Destination:=Range("N3") ' copies column 3(C) to 14(N) yellow to yellow
    Range("D3:D" & lr).Copy Destination:=Range("O3") ' copies 4(D) to 15(O) green to green
    Range("F3:F" & lr).Copy Destination:=Range("L3")  ' copies 6(F) to 12(L) blue to blue
    Range("G3:G" & lr).Copy Destination:=Range("M3") ' copies 7(G) to 13(M) red to red
    Range("C2").Select

End Sub

-mark

[mperrah]

This was an attempt at an array solution, but I cant get it to work...
if anyone wants to play with it be my guest.
I think I'm looping through the array variables to much, that over rights the values, but not sure

Sub vbax52603()
'move data from columns to other on same sheet

Dim x, s, d, lr As Long
Dim mySrc, myDst As Variant

lr = Cells(Rows.Count, 1).End(xlUp).Row
ReDim mySrc(1 To 4)
ReDim myDst(1 To 4)
mySrc = Array("C", "D", "F", "G")
myDst = Array("N", "O", "L", "M")
   
    For d = LBound(myDst) To UBound(myDst)
        For s = LBound(mySrc) To UBound(mySrc)
            For x = 3 To lr
                Cells(x, myDst(d)).Value = Cells(x, mySrc(s)).Value
            Next x
        Next s
    Next d

End Sub

[JLandrum]

Hey Mark, thanks for the help. The first one worked for moving the data, any ideas how to sorted by column 11,44,33. Any and all help is greatly appreciated, thank you

[SamT]

move THE DATA from each column listed from 1, 2, 3, 4, to 11, 22, 33, 44 in VBA

I would use Brute Force on so few moves.
Copy 1
11.PasteSpecial(xlValues)
1.clearContents
Copy 2
22.PasteSpecial(xlValues)
2.ClearContents

You can make the code read better by

LastRow = CStr(Cells(Rows.Count, 1).End(xlUp).Row)
Dim EmpSatus1 As Range
Dim EmpStatus11 As Range
Etc

Set EpmStatus 1 = Range("C3:C" & LastRow)
Set EmpStatus11 = Range("N3:N" & LastRow)
Etc

EmpStatus1.Copy
EmpStaus11.PasteSpecial xlValues
EmpStatut1.ClearContens
Etc

Make it easy: Copy each of the three sections above and paste them into the code 4 times, then edit the numbers and Column Letters

[JLandrum]

Thanks for the help but you lost me, Where do I enter this, into the existing code?

[mperrah]

What column do you want to sort by?
Do you mean first sort by the column with 11 in Row 2 (which is column N of your sample)
then by 44 (which is column M)
and then by 33 (which is column L) ?

if so try this:

Sub vbax52603_Sort()

lr = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveWorkbook.Worksheets("Sheet1").Sort
    Range("L2:O" & lr).Select
    .SortFields.Clear
    .SortFields.Add Key:=Range("N3:N" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("M3:M" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("L3:L" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    .SetRange Range("L2:O" & lr)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

[JLandrum]

Works great, do I just change Descending to ascending to get a,b,c, instead of c,b,a

[mperrah]

Got my array solution working, yeah...

Sub vbax52603B()
'move data from columns to other on same sheet

Dim x, s, d, lr As Long
Dim mySrc, myDst As Variant

lr = Cells(Rows.Count, 1).End(xlUp).Row
ReDim mySrc(1 To 4)
ReDim myDst(1 To 4)
mySrc = Array(3, 4, 6, 7)
myDst = Array(14, 15, 12, 13)

   
    For d = LBound(myDst) To UBound(myDst)
        For s = LBound(mySrc) To UBound(mySrc)
            For x = 3 To lr
                Cells(x, myDst(d)).Value = Cells(x, mySrc(s)).Value
            Next x
        Next s
    Next d

End Sub

and yes on the ascending:

Sub vbax52603_Sort()

lr = Cells(Rows.Count, 1).End(xlUp).Row

With ActiveWorkbook.Worksheets("Sheet1").Sort
    Range("L2:O" & lr).Select
    .SortFields.Clear
    .SortFields.Add Key:=Range("N3:N" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("M3:M" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SortFields.Add Key:=Range("L3:L" & lr) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("L2:O" & lr)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

[mperrah]

jlandrum:
just an observation, do you just want the dates sorted only
- excluding the names from Column A ?
This sort does that,
but if you need the names to stay in the corresponding rows as the dates this would need altering FYI
- mark

[JLandrum]

I tried the new one, didn't work for me. Thank for all your help

[SamT]

Thanks for the help but you lost me, Where do I enter this, into the existing code?

You don't

You use it as an Algorithm (Pattern) to write your own code.

Learn by doing. We will help you perfect your code.

Truth be told, I'm working on a chicken coop in the yard and only have time for suggestions. (when I'm on a break.

[JLandrum]

Mark, thanks for your info. I was able to make a few changes and learn what you did. Im pretty decent at excel but VBA is a new area for me. Thanks for the lesson. Im sure I will be back on as im learning more. Thanks again

[mperrah]

Glad to help.
I'm pushing ten years frequenting this forum. (just figured out how to change my avatar Lol)
Its awesome the kind of help you can find.
I tend to be verbose when asking for help, I think it scares some away.
I have found being open to having a solution that gets there in a way you hadn't thought of is key.
asking what your goal is rather than the method to get there is always a good starting point.

cheers
-mark

[mperrah]

by the way, what part didn't work on the newer version
the sorting or the array

[JLandrum]

The array didn't, sorting work after I changed to ascending.

[JLandrum]

Mark, here something else if you would like to help me. I have a list of names in column A. First M Last, not everyone has a middle. Im trying to just separate the first and last into another column. I used a formula which worked but curious is there a way to sort a cell that has first m last. but needs to be sorted by last. Thanks

[mperrah]

I would split the names to separate cells and sort after that.
there are plenty of examples how to do that in this forum (I'm not the best at string handling)

found this to start:

Sub SplitNames()
Dim LR As Long, i As Long, X As Variant
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        X = Split(.Value)
        .Offset(, 1).Resize(, UBound(X) + 1).Value = X
    End With
Next i
End Sub

[mperrah]

I see on the array.
If the data in the columns are missing the array fails.
I did a test with all the columns full of sample data, sorry