Search by columns headers and paste all matching columns to another sheet

**maamon** · 08-12-2021, 09:41 AM

Hi, would you help me with a macro (because I think there is no other option) which will allow to:

1. Find a match picked from a dropdown list (sheet2) among column headers in sheet1
2. Copy all columns which will match
3. Paste those in sheet2 all next to each other

For Example, if in sheet2 from dropdown list was chosen "Wera", then macro looks into each column headers in sheet1 to find match, then it will copy all columns (header and data) and paste in in sheet2.
Please take a look on the file that I've prepared. In real file there is much more columns and rows.

Thanks,M.

**jindon** · 08-12-2021, 10:16 AM

To Sheet2 code module

Please Login or Register  to view this content.

**Artik** · 08-12-2021, 10:19 AM

If the found columns (to be copied) will be less than about 60, then the macro below should be fine.
Place the routines in the Arkusz2(Sheet2) module.

Please Login or Register  to view this content.

Artik

**Sintek** · 08-12-2021, 10:43 AM

Another Option...As per column limitation...

Please Login or Register  to view this content.

**jindon** · 08-12-2021, 10:34 PM

Originally Posted by Artik

If the found columns (to be copied) will be less than about 60, then the macro below should be fine.

Please Login or Register  to view this content.

Artik

Originally Posted by sintek

Another Option...As per column limitation...

Why keep posting such incomplete codes where there is a work around.
I didn't use this, because it still need to loop.

Please Login or Register  to view this content.

**Sintek** · 08-13-2021, 02:23 AM

@ jindon...

But why oh why...In previous thread here when you referenced Excel limitations, I assumed the functionality of this fell within the Column Sort Limitation...Although not specifically pointing to non contiguous column grouping...If the column count is within the limitations...Does it not render same result?

I have run all the codes with Column count < limit ...and the same results are rendered...I understand that one should ideally make provision for possible limitations but if the sample data falls within theses limitations, surely, the snippet could be used?

**jindon** · 08-13-2021, 02:33 AM

That's nothing to do with this "LIMITATION".

Sub-sting of Range object needs to be less than 256 characters.

**Sintek** · 08-13-2021, 02:50 AM

jindon...Please forgive my ignorance...When I joined this forum, I knew nothing about excel...Only by following posts and guidance of members such as yourself & others have I managed to get a better understanding of VBA...I have had no education or followed any tutorials...I only learned what I needed at the time to get the job done...So to say...And yes, by now I should have done further investigation into the "nitty gritty" of Excel but this is a side line hobby and not my career...happy to learn as I go along...Thank you once again for your valuable lesson...

**jindon** · 08-13-2021, 02:57 AM

What I'm trying to say is that are you happy to receive the code that has a knowing problem when you ask question?

**Sintek** · 08-13-2021, 03:03 AM

No I suppose not...Should supply code that will accommodate all possible eventualities...Just in case...And not supply code that just gives the correct result for supplied dataset...

**maamon** · 08-13-2021, 09:22 AM

Hi, I have tried this on the mockup file and it was working just fine. But right now I replaced data with real ones and it shows me me error: run-time error "13": type mismatch. And it's pointing to:
x = Join(Filter(.Parent.Evaluate("if(" & .Rows(1).Address & "='" & _
Me.Name & "'!d2,address(1,column(" & .Address & "),4)&"":""&address(" & _
LR & ",column(" & .Address & "),4))"), False, 0), ",")

**jindon** · 08-13-2021, 09:27 AM

Your picture files are invalid so can not open.

1) Right click on Sheet2 tab and [View Code]
2) Paste the code onto the right pane and close vbe window.

It will execute when you change D2.

**maamon** · 08-13-2021, 10:10 AM

Hi, All works now. In the real file it were email addresses in the dropdown list to lookup and once I have removed "@company.com" it started working fine. Once again many thanks!

Search by columns headers and paste all matching columns to another sheet

LinkBack

Thread Tools

Rate This Thread

Display

Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Re: Search by columns headers and paste all matching columns to another sheet

Thread Information

Users Browsing this Thread

Similar Threads

Returning the headers of all columns with a matching value

Allocation of columns to other sheet based on matching headers

Allocation of columns to other sheet based on matching headers

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

[SOLVED] Search Multiple columns for string, display matching results on different sheet.

[SOLVED] Search Copy and Paste two columns in different sheet

Auto filter multiple columns matching with searched headers

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions