Copy columns to a new workbook if the headers row matched

**haitran** · 12-13-2017, 10:57 AM

Hi everyone,
I'm very new to VBA, Macros so please forgive if there is any mistakes describing the situations.
So basically I want to create a macro that copy columns from a Sheet, these columns have to match headers in another Sheet, to a new workbook.

Workbook 1 has Sheet Settings (Sheet 1) and Sheet Inputfile (Sheet 2).
Sheet Inputfile has data with columns headers for each columns.
Sheet Settings has Column Name column, user will enter the columns that they want to copy in this column (the columns name are from Sheet Inputfile).
I want to create a macro that copy columns from Sheet Inputfile to a new Workbook, these columns's headers have to matched the headers (in the Output line) in Sheet Settings.

For more details you can see the attached file.

Any help would be appreciated.
Thank you.

**Bernie Deitrick** · 12-13-2017, 12:11 PM

Try if like this:

Please Login or Register  to view this content.

**haitran** · 12-13-2017, 01:46 PM

Originally Posted by Bernie Deitrick

Try if like this:

Please Login or Register  to view this content.

Thank you very much for the code, it helps me a lot.
I missed a detail in the question.
In the Output line of Sheet Settings (starts from Cell "E10"), user will place the columns in some orders (any order they want) and I want the new workbook has the columns with the same order as the user placed. Can you please help me with this?
Again thank you very much for the help.

**Bernie Deitrick** · 12-13-2017, 03:00 PM

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

and change the -1 to -9 in this line:

Please Login or Register  to view this content.

**haitran** · 12-13-2017, 03:36 PM

Hi Bernie,
Because in the Output line , the headers are on 1 row so it will be "E10", "F10", "G10", etc.. so should the code be like this instead ?
.Range(.Range("E10"), .Cells(10, .Columns.Count).End(xlToLeft))

And can we just copy from row 10 till last row instead of the entire column (so we dont have the blank space in the new workbook) ?

Thank you very much.

**Bernie Deitrick** · 12-13-2017, 03:55 PM

Yes:

Please Login or Register  to view this content.

**haitran** · 12-13-2017, 04:08 PM

There is a problem when I tried the code

Please Login or Register  to view this content.

It only shows the last column in the new workbook (column Test Name). Do you have any idea why?

And how can I copy only from the row 10 instead of the entire column ?

Thank you very much.

**Bernie Deitrick** · 12-13-2017, 04:15 PM

Originally Posted by haitran

Do you have any idea why?

Because I wasn't thinking :/

You also need to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**haitran** · 12-13-2017, 05:11 PM

It works now, thank you.
Just 1 last question and I hope you don't mind. How can I copy from the row 10 instead of the entire column ?
Thank you very much.

**Bernie Deitrick** · 12-13-2017, 05:41 PM

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

But - you don't really want to paste to an entire column when you just copy one cell. Where do you want that cell pasted? We had been using

rngF.EntireColumn.Copy shtNew.Columns(rngC.Column - 4)

Perhaps you should show an example of what you want (in a third sheet instead of a new workbook) based on values in those two example sheets.

**haitran** · 12-13-2017, 05:43 PM

Sorry to confused you.
I want to copy from the row 10 to the last row, not just only row 10. Because from row 1 to 9 there is only blank space.

**Bernie Deitrick** · 12-13-2017, 06:06 PM

I understand now - just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

**haitran** · 12-13-2017, 06:15 PM

That almost perfect.
But is there any way to copy from row 10 specifically till the last row? Because sometimes in some excel files, there might be some cells that have value above the headers and I just want to get rid of them in the new workbook.
Thank you very much.

**Bernie Deitrick** · 12-13-2017, 06:21 PM

That should only copy from the header down, unless the header is not in row 10:

Please Login or Register  to view this content.

will ensure that the copy starts at row 10 and ends at the last row (if your usedrange starts in row 1) ... Not sure where your extra values are or what is being copied that you don't want....

**haitran** · 12-13-2017, 06:32 PM

That's perfect.
Thank you very much for the help.

Copy columns to a new workbook if the headers row matched

LinkBack

Thread Tools

Rate This Thread

Display

Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Re: Copy columns to a new workbook if the headers row matched

Thread Information

Users Browsing this Thread

Similar Threads

Match 2 Columns in two sheets; copy an offset Column of matched Row to Sheet 1

[SOLVED] Copy columns of different headers

Copy columns to other worksheet with same headers

macro to add columns after existing columns and copy/paste headers and formulas

[SOLVED] Averaging Based on Defined Row Value and Matched Column Headers

Search multiple workbook headers and copy column entries to another workbook

Match two Columns of two Sheets and Copy Matched Rows to a Third Sheet

Bookmarks

Bookmarks

Posting Permissions