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?
 
NorieConnect With a Mentor VBA ExpertCommented:
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

0
 
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.
0
 
NorieVBA ExpertCommented:
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

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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.
0
 
NorieVBA ExpertCommented:
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.
0
 
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
0
 
Pancake_EffectAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.