AndyC1000
asked on
How to copy columns from one excel sheet to another based on column name
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
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
If this is a one-time job then the easiest way is to copy then entire data over and then delete the unwanted columns.
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.
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.
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:
Thanks
Rob H
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.Criteria - area as setup above
Destination - column headers on destination sheet.
Thanks
Rob H
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
ASKER
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?
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?
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.