Macro to Swap/Replace a entire column by using the header name?

[HaoXu]

Hi guys:

I have a lot of columns named different ways, lets suppose that their names are "products","sales","vendor","place","shops","customers" etc...

What i need is a macro that will copy and paste the information that is in the column "sales" into the column of "customers" for example.

I have been looking for a macro, but didn´t find a single one that would use the header name of the column instead of column a, b, c, d... I need this because i have a lot of excels and the columns are not always in the same order.
(sometimes comes like: sales, vendor, shops... sometimes comes like: shops, sales, vendor...).

Thank you in advance.

 

[MrJoosten]

Hi HaoXu,

Are the names "products","sales","vendor","place","shops","customers" etc in row 1?

 

[MrJoosten]

If so, I used this to copy sales to vendor.

Sub copyPaste()
Dim FirstRow As Long
Dim LastRow As Long
Dim FirstColumn As Long
Dim LastColumn As Long
Dim col As Long

LastRow = Cells(Rows.Count, 1).End(xlUp).Row
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
FirstColumn = 1
FirstRow = 1

'copy sales
With ActiveSheet.Rows(1).EntireRow
.Find(What:="sales", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select
End With

col = ActiveCell.Column
Range(Cells(2, col), Cells(LastRow, col)).copy

'paste sales values in column vendor
With ActiveSheet.Rows(1).EntireRow
.Find(What:="vendor", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Select
End With

col = ActiveCell.Column
Cells(2, col).PasteSpecial xlPasteValues

End Sub

Good luck!

 

[HaoXu]

Hi MrJoosten:
Thank you so much for your reply, it was exactly what i was looking for!
Could you help me with another little thing?
Now i´m looking for a dynamic macro that can set automatically the range of column A B and C and sort it. The extension of data is not always the same in every excel i have to work on. That´s why i need a macro that can automatically set the range and sort it. (Personalized sorting, first by column A, then B and then C)

Thank you so much for the previous answer, helped me a lot!

 

[MrJoosten]

Hi HaoXu,

You're welcome.

I don't have much experience with sorting. Playing with the macro recorder and adjust it a bit gives me this:

Sub testt()

Dim lrC1 As Long
Dim lrC2 As Long
Dim lrC3 As Long

'Set lastrows for each column
lrC1 = Cells(Rows.Count, 1).End(xlUp).Row
lrC2 = Cells(Rows.Count, 2).End(xlUp).Row
lrC3 = Cells(Rows.Count, 3).End(xlUp).Row

'clear fields
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    
'Sort data Column1
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & lrC1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:A" & lrC1)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
    
    
'Sort data Column2
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & lrC2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("B2:B" & lrC2)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
    
'Sort data Column3
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C" & lrC3), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    
With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("C2:C" & lrC3)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

    
End Sub

I hope you can adjust it to your needs.

Good luck!

 

[HaoXu]

Hi MrJoosten:

Thank you so much for your replies, i really appreciate your work!

It is almost what i was looking for! The problem is that this code is only sorting me column C.
In column A i have the numbers of letters that has each cell of column B, and in column C i have the "product ID number"
For example:

A B C
10 Sunglasses 123456
9 Necklaces 123457
5 Rings 123458
8 Bracelet 123459

The number of rows is different for each excel of products, what i would need is a macro that can automatically sort B and C based on A:

A B C
5 Rings 123458
8 Bracelet 123459
9 Necklaces 123457
10 Sunglasses 123456

This way i can have "Product" sorted by number of letters and still having his correct ID sticked to each product.

Thank you so much in advance for your help, i really appreciate it!

 

[MrJoosten]

Hi HaoXu,

You can do this by selecting your data and place a filter over it right? (ctr + shift + L)
Then sort the data and it will stay in the right format.

 

[HaoXu]

Ok MrJoosten!

Thank you so much for your helping