VBA Excel: Copy/Paste a column range to another sheet IF column headers match

**hwatson86** · 06-06-2016, 09:39 PM

I am building a performance appraisal document. The sheet "Sheet1" acts like a form where users will select the current month from a dropdown box (M5) and then fill out their results in the cells below (M6:Mxx).

Another sheet, "Sheet2" is a log sheet. Cells D7:I7 are column headers and each cell is a month name(i.e. d7=July, e7=August). These headers are in the same format as the dropdown in the Monthly Template sheet.

When the user clicks a Command Button, I want the sheet to match the value in "Sheet1(M5)" to "Sheet2(D7:I7)" and paste the range of results (Sheet1(M6:Mxx) below the matching value in Sheet2

i.e. if Sheet1M5 = July and Sheet2D7 = July, copy range from M6 down to the first empty cell, into Sheet2 D8 onwards.

Could I please have your help to write the code that will achieve this?

Thanks Hayley

**TMS** · 06-06-2016, 10:09 PM

Something like:

Please Login or Register  to view this content.

**hwatson86** · 06-06-2016, 10:13 PM

Hi TMS,

Thanks. With the line

Sheet2.Range("A1:O1"), _

Can you explain why I would use this range when the fields I want to match are in D7:i7?

Thanks
Hayley

**hwatson86** · 06-06-2016, 10:22 PM

Originally Posted by TMS

Something like:

Please Login or Register  to view this content.

I'm getting a runtime error 1004 : Unable to match property of Worksheet Function

**TMS** · 06-06-2016, 10:30 PM

I screwed up

. Because I mis-read the question and assumed the headers were in row 1, Should have been D7:I7. However, I start with A7 because I want to get a column number, not an index relative to D7, and go to O7 to allow 12 months.

Please Login or Register  to view this content.

**Trebor76** · 06-06-2016, 11:26 PM

Hi Hayley (and fellow Aussie

),

Here's my attempt:

Please Login or Register  to view this content.

Regards,

Robert

**Trebor76** · 06-07-2016, 12:29 AM

And here's my next attempt using some of TMS's nifty code:

Please Login or Register  to view this content.

Regards,

Robert

**hwatson86** · 06-07-2016, 06:22 AM

Originally Posted by Trebor76

And here's my next attempt using some of TMS's nifty code:

Please Login or Register  to view this content.

Regards,

Robert

Hey Robert, it is pasting to the new sheet but is going into column A and B? It also seems to be copying information below the first blank cell it encounters?

**TMS** · 06-07-2016, 06:32 AM

This should do everything ... I also missed the requirement for "next available row" so that's fixed here. Commented so you can adapt it if necessary:

Please Login or Register  to view this content.

**TMS** · 06-07-2016, 06:36 AM

Robert's code is matching against D7:O7 and therefore returning an index relative to that range. As I said previously, that index would need to be modified for it to be a valid column reference ... and I can't see that has been done.

**TMS** · 06-07-2016, 06:50 AM

Just to clarify, if you input data for January, it will match "January" against D7:O7 and find "January" in cell D7, the first cell of the range, and return 1.

It then uses 1 as the column reference and copies the data to column 1, that is column A. May's data would be posted under February.

**hwatson86** · 06-07-2016, 06:52 AM

Thanks again for your assistance. I am however getting a "Runtime error 9 - subscript out of range" error here
lColumn = Application.WorksheetFunction.Match( _
Sheet1.Range("M5"), _
.Range("A7:O7"), _
0)

**TMS** · 06-07-2016, 06:57 AM

Post your workbook, or a sample with the input and output.

I use the headings on Sheet 2 as the Data Validation List on Sheet 1 so there should not be a subscript out of range.

Could mean you have different spellings or trailing spaces, etc

**Trebor76** · 06-07-2016, 07:01 AM

Hey Robert, it is pasting to the new sheet but is going into column A and B?

Change this line of code...

Please Login or Register  to view this content.

...to this:

Please Login or Register  to view this content.

It also seems to be copying information below the first blank cell it encounters?

Not sure what you mean by this. It should paste to the next available row in the desired column in Sheet2. Change the code as I've outlined above and try it again - hopefully all will be good.

Robert

**TMS** · 06-07-2016, 07:09 AM

Please Login or Register  to view this content.

I like it, heh, heh. Don't know if it has any efficiency ramifications but I doubt it will be an issue.

**Trebor76** · 06-07-2016, 07:49 AM

Yeah looking across 16,384 columns probably isn't most efficient way but it's just a once off so shouldn't be an issue. I've seen plenty of MATCH code referencing a whole column so that's 1,048,576 rows and it seems to run OK.

Glad you liked it

Cheers,

Robert

**TMS** · 06-07-2016, 08:05 AM

Glad you liked it

Made me laugh

VBA Excel: Copy/Paste a column range to another sheet IF column headers match

LinkBack

Thread Tools

Rate This Thread

Display

VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Re: VBA Excel: Copy/Paste a column range to another sheet IF column headers match

Thread Information

Users Browsing this Thread

Similar Threads

copy and paste columns based on column headers

How to Match a column with another sheet's column and copy paste the Entire Row&Column aft

Loop through column in first sheet, match value with sheet names in workbook, copy/paste.

Copy data from one variable range (column) from 1st sheet to paste and append on Sheet 2

[SOLVED] Match value(s) in column with headers of another sheet and paste a cell value from column

[SOLVED] Match, Copy and Paste Column Headers - varying number of columns

[SOLVED] VBA code to copy column data only if headers match

Bookmarks

Bookmarks

Posting Permissions