Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-13
7
Medium Priority
?
136 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:Pancake_Effect
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:Frank White
ID: 37715801
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
 
LVL 35

Expert Comment

by:Norie
ID: 37715849
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
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37716416
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Expert Comment

by:Norie
ID: 37716431
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
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 37716517
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
 
LVL 35

Accepted Solution

by:
Norie earned 1500 total points
ID: 37716631
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
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 37768217
Thanks!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question