Forum Discussion
macro to search headers and copy/paste values into another worksheet
Hi,
I'm new to macros and learning now.I want to get my excel files arranged in specific order.For e.g,Name,Age,Phone,Address,Sum.
The headers are not in order in all the files.So I want my macro to search for the specific header in the sheet and find,copy and paste it into the template sheet with desired header order.Sample files as given below:
So,first the macro searches for the Name header and copy,paste the values into the corr.header,then Age and so on and so forth.Could anybody please help ?Thanks a lot.
Pushpa985 As I don't know how you have organized your files it's difficult to give a distinct answer. A good place to start learning about PQ would be the site in the link below. Read through the first page to understand what it's all about. Then, towards the end you'll find a list with 20 articles. Number 9 is probably going to be most useful to you (connecting to files from a folder). But take your time and scan through everything from the beginning so that you don't miss some of the basic principles.
5 Replies
Pushpa985 In stead of learning VBA/macros, I would recommend you to learn Power Query (PQ) in stead. This kind of task is super easy in PQ as you can connect to tables (where ever they are) and append them. It does not matter in what order the columns are, as longs as the column names are the same. PQ will match the column names automatically and you can sort them in any order you desire. Set it up once and you can repeat the process over and over again. I attached a small example in the attached file.
- Hi Riny..I tried Power Query and thank you so much for introducing me to this. Could you please help me with 1 question: I tried and achieved the result with 1 file.Now I want to save that query and repeat the process whenever I want and on the files that I choose(as mentioned by you).How to achieve that ? I try reading and doing. But it leads to nowhere. Thanks in advance.
Pushpa985 As I don't know how you have organized your files it's difficult to give a distinct answer. A good place to start learning about PQ would be the site in the link below. Read through the first page to understand what it's all about. Then, towards the end you'll find a list with 20 articles. Number 9 is probably going to be most useful to you (connecting to files from a folder). But take your time and scan through everything from the beginning so that you don't miss some of the basic principles.
- ok Riny..thanks a ton for the reply. I appreciate your suggestion and try it and let u know.Have a nice day .
