Solved

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

Posted on 2012-03-13
7
128 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
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:DaFranker
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 33

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

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 33

Accepted Solution

by:
Norie earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 39
conditional formatting formula error 4 16
Excel - Row Height +1 VBA 2 23
Changing absolute cell references 3 16
A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now