Change Position of Column and Column Name

I have an Excel spreadsheet that I receive and each time I receive the file I am forced to place the columns in a different position and also rename the column headers.  Within Excel VBA how can I change the column header names and the column position.  See tab names called Original and Modified.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wayne Taylor (webtubbs)Commented:
Nothing attached.
upobDaPlayaAuthor Commented:
Sorry..have attached now
Wayne Taylor (webtubbs)Commented:
Is it only those 3 columns? Will there ever be more columns of data? Are they always in the order showing in the "Original" sheet?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Martin LissOlder than dirtCommented:
Click the button (or run the macro in Module1)

With my code it doesn't matter how many columns there are, what they are called, or what order they are in.
Wayne Taylor (webtubbs)Commented:
Looks like we'll still need the answers to my questions above as Martin's code doesn't produce the desired output.
Martin LissOlder than dirtCommented:
You're right. I thought the columns just needed to be reversed.
upobDaPlayaAuthor Commented:
There will always be more than 3 columns but they will always be in the same order for the Original tab.  I only used 3 as an example.
Martin LissOlder than dirtCommented:
I've added a worksheet to this workbook that contains the desired order of the columns. It assumes that no matter how many columns there are on the Original worksheet, that the oder will always be the same. In other words original column 1 will always become column 2, original column 2 will always become column 3, original column 3 will always become column 1, and so on. You will need to update the Col Order worksheet with the full order.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upobDaPlayaAuthor Commented:
Hi Matin, this is awesome !  Can you explain to me the .UsedRange.Columns.Count - How is UsedRange defined ?  Does it go to the first column and then go right to determine how many used columns I have ?
Martin LissOlder than dirtCommented:
Does it go to the first column and then go right to determine how many used columns I have ?
Yes, but Excel sometimes has trouble determining first & last rows and columns. so if that code doesn't work for you you can change line 11 to

lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).ColumnngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

Sub RearrangeColumns()
Dim lngCol As Long
Dim lngLastCol As Long
Dim wsOrder As Worksheet

Application.ScreenUpdating = False

Set wsOrder = ThisWorkbook.Worksheets("Col Order")

With ActiveSheet
    lngLastCol = .UsedRange.Columns.Count

    For lngCol = 1 To lngLastCol
        .Columns(lngCol).Copy Destination:=.Columns(lngLastCol + wsOrder.Cells(1, lngCol))

.Range(.Cells(1, 1), .Cells(1, lngLastCol)).EntireColumn.Delete

For lngCol = 1 To lngLastCol
    .Cells(1, lngCol) = .Cells(1, lngCol) & "-BWE"
End With
Application.ScreenUpdating = False

End Sub

Open in new window

Martin LissOlder than dirtCommented:
Please don't forget to close this question.
Wayne Taylor (webtubbs)Commented:
An alternative to specifying the order in a separate workbook is to specify it in the macro. It also allows you to specify the new column header text. You can add as many as you want.

It will also ignore any columns that you have not specified.

Sub OrderColumns()

    Dim ColumnOrder As Variant
    Dim i As Integer, fnd As Range

    ColumnOrder = Array(Array("Invoice", "Invoice-BWE"), _
                        Array("Product", "Product-BWE"), _
                        Array("Manager", "Manager-BWE"))

    For i = UBound(ColumnOrder) To 0 Step -1
        Set fnd = Range("1:1").Find(ColumnOrder(i)(0), , xlValues, xlWhole)
        If Not fnd Is Nothing Then
            fnd.Value = ColumnOrder(i)(1)
            Columns("A:A").Insert Shift:=xlToRight
        End If
End Sub

Open in new window

upobDaPlayaAuthor Commented:
upobDaPlayaAuthor Commented:
Learned a lot..the various approaches gave me something to think about (had not considered an array) but I implemented Martins as it best fit my problem.  Will fool around with the array though..
Martin LissOlder than dirtCommented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
upobDaPlayaAuthor Commented:
Thank you..I will take a look...I am trying to soak up as much VBA as I can so it appears your articles will be a good resource.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.