Excel: Move/Copy/Create Columns with data Macro?

I'm curious if it's possible to move/copy/paste Columns with data, using a Macro?

Let me explain:

We have a program that spits out data in the wrong order.

Anyone have a macro that we can easily use/modify to run that will copy/paste/move columns over to where we can it?

Along with that, can we have it MAKE blank columns where we specify so we can input custom columns the program did not make for us?

we're needing to modify the values once in a while, so hopefully it's not to hard to specify where to change/create them.



-I have a excel sheet attached that shows into more detail what we want.


Thanks!!!!!!!
Format-Example.xlsx
LVL 4
Pancake_EffectAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Frank WhiteCommented:
I'm not sure I understand why a macro is necessary for this. You can have a different worksheet, in parallel, that continuously displays the data in the appropriate order.

For example, in the example document, in cell A1 of Sheet2, enter "=Sheet1!J1", hit enter, then copy cell A1 and paste it in cells A2 and A3. You can repeat this process for each column (changing the letter J identifying which column from Sheet1 to get the data as appropriate) and for as many rows as you need.

If there are other conditions which make the above not an option, then yes it is possible with VBA, but if you expect to have very large amounts of data (which would be the case for hospital patient records, for example), or if you want to switch the columns around "in place" (i.e. the very columns themselves being reordered in the same sheet, not copied in a different order elsewhere), then things could get tricky.
NorieAnalyst Assistant Commented:
This sort of thing shouldn't be difficult but I've looked at the file and there appears to be columns in the 'after' that don't exist in the 'before'.

For example in column K of the 'after' you have the header ARADTM but that doesn't appear in the 'before'.

In fact the headers of columns K-N (ARADMTM, ARDISTM, ARAGE,   ARPHY1,  ARPHYNM1)
 in the 'after' don't appear in the 'before'.

There also seems to be columns missing.

Are there mistakes, perhaps simple typos in the file?

Anyway, if we take the first 7 columns in the 'after', which all have matches you could use code like this.
Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim arrCols
Dim I As Long

    arrCols = Array(10, 6, 16, 4, 5, 2, 7)

    Set wsSrc = Worksheets("Sheet1")
    Set wsDst = Worksheets.Add

    Set rngDst = wsDst.Range("A1")

    For I = LBound(arrCols) To UBound(arrCols)

        Set rngSrc = wsSrc.Columns(arrCols(I))

        rngSrc.Copy rngDst

        Set rngDst = rngDst.Offset(, 1)

    Next I

Open in new window

Pancake_EffectAuthor Commented:
Sorry I must have copied and pasted the titles too hastily. The bottom row was suppose to be the same names as the top just in a different order, but with inserted blank columns to represent if I wanted to insert extra blank columns.

I tried running your script but it just get's an error. I'm copying and pasting it into a module, is there something I'm missing? (Sorry I'm a bit of a noob) Thanks!

Edit:

-Attached is a sheet of what the original data looks like (with fake example data)

I also have the code inserted if you look at the modules.
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

NorieAnalyst Assistant Commented:
The code I posted works, but you would need to add Sub/End Sub at the start finish.

I'm afraid there's no attachment though.
Pancake_EffectAuthor Commented:
Sorry! Forgot to attach document. After attaching the sub/end it started working! (sorry said I was a noob haha)

The document attached now shows what a real live document would somewhat look like for a better example.

If I want to change the order and specify which columns which part do I change in your code?

Thanks!
Test-Document.xlsm
NorieAnalyst Assistant Commented:
This seems to  work, one thing to note though is that it does create a new worksheet with the columns re-ordered as required.
Sub move()

Dim wsSrc As Worksheet
Dim wsDst As Worksheet
Dim rngSrc As Range
Dim rngDst As Range
Dim arrCols
Dim I As Long

    arrCols = Array(8, 5, 15, 3, 4, 1, 6, "B", 7, "B", 17, 18, 19, 20, 21)

    Set wsSrc = Worksheets("Sheet1")
    Set wsDst = Worksheets.Add

    Set rngDst = wsDst.Range("A1")

    For I = LBound(arrCols) To UBound(arrCols)
        If arrCols(I) <> "B" Then

            Set rngSrc = wsSrc.Columns(arrCols(I))

            rngSrc.Copy rngDst

        End If

        Set rngDst = rngDst.Offset(, 1)

    Next I
End Sub

Open in new window

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
Pancake_EffectAuthor Commented:
Thanks!
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
Microsoft Excel

From novice to tech pro — start learning today.