Solved

How to copy columns from one excel sheet to another based on column name

Posted on 2013-05-15
8
437 Views
Last Modified: 2013-05-19
Dear all,

I've been given a sheet with alot of data.  I want to move specific columns into new excel sheets so that i can import it into a database.

Is there any easier way than manually copying and pasting?

It would also be ideal if a string in a cell i.e. B3 of original sheet be copied into a column on the second sheet described above, with a column name that I define.

Thanks
0
Comment
Question by:AndyC1000
8 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
If this is a one-time job then the easiest way is to copy then entire data over and then delete the unwanted columns.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
Comment Utility
Hi,

Whether this is a recurring task, or not, the process can be automated.

You will need to provide the complete specification of your requirements though.

Obviously, if there is a deadline, then the time taken to prepare an automated solution may be longer than actually moving the data manually (as ssaqibh mentioned).

BFN,

fp.
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
You could use the Advanced Filter function.

On the Destination sheet put only the column headers that you require.
Setup a small table with one column header and blank cell below or something that will capture all data.

In the AF function:
Data - source sheet
Criteria - area as setup above
Destination - column headers on destination sheet.
When run, only the columns for which you have headers will be copied over.

Thanks
Rob H
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Here's a macro. This one copies columns A, C and D from Sheet1 to Sheet2 but those things are easily changed.

Sub CopySomeColumns()
Dim arrCols
Dim lngIndex As Long
Dim lngCol As Long

' These are the column numbers to copy
arrCols = Array(1, 2, 4)
' This starts the pasting at column "A"
lngCol = 1

Application.ScreenUpdating = False

For lngIndex = 0 To UBound(arrCols)
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Columns(arrCols(lngIndex)).Select
    Selection.Copy
    Worksheets("Sheet2").Activate
    Worksheets("Sheet2").Columns(lngCol).Select
    ActiveSheet.Paste
    lngCol = lngCol + 1
Next

Application.ScreenUpdating = True
End Sub

Open in new window

0
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

 

Author Comment

by:AndyC1000
Comment Utility
Thanks Martin that's what I was after.

To extend the macro to copy i.e. columns 10, 11 and 12 into sheet 3, can this be included in the same macro? Or will I need to create another macro each time I need to copy different columns into new sheets?

I copied and adjusted the for statement for the above scenario into the same macro is this the best way?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Just change line 7. For example to do what you mention just change 1, 2, 4 to 10, 11, 12. You also need to change Sheet2 to Sheet3 if that's your destination sheet.
0
 

Author Comment

by:AndyC1000
Comment Utility
Great, that worked.

Is it possible to copy column values (i.e column 2) from the third row downwards into the destination sheet?

I'm trying to avoid any manual changes to the data.  I can't delete the data above the table headers because it includes descriptors.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
Sure. This copies the data from row 3 downward from sheet1 to sheet3.

Sub CopySomeColumns()
Dim arrCols
Dim lngIndex As Long
Dim lngCol As Long
Dim lngLastRow As Long

lngLastRow = ActiveSheet.UsedRange.Rows.Count
' These are the column numbers to copy
arrCols = Array(10, 11, 12)
' This starts the pasting at column "A"
lngCol = 1

Application.ScreenUpdating = False

For lngIndex = 0 To UBound(arrCols)
    Worksheets("Sheet1").Activate
    Worksheets("Sheet1").Range(Cells(3, arrCols(lngIndex)), Cells(lngLastRow, arrCols(lngIndex))).Select
    Selection.Copy
    Worksheets("Sheet3").Activate
    Worksheets("Sheet3").Cells(3, lngCol).Select
    ActiveSheet.Paste
    lngCol = lngCol + 1
Next

Application.ScreenUpdating = True
End Sub

Open in new window

0

Featured Post

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

Join & Write a Comment

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

17 Experts available now in Live!

Get 1:1 Help Now