Hi, You didn't provide any feedback for your first query. As a matter of fact, I'm not really happy with the code I provided you with. First there would be ways to do this using excel formula such as vloopkup and match I used once, vlookup looks for the data, and match should tell vlookup to look in a specific columns given as first argument. To be honest I'm not comfortable with advance Excel formula and to some extend, with big data volume, reference formula can slow things done such as opening a workbook.
As for my code in the futur, you might want to change this part: jCol(0) = 6 jCol(1) = 7 jCol(2) = 8 ... Why? Because it's really static. Like if you want to insert a column and perform any operation there, the code will have to be updated (unless you delete the column before running the code)...further if your software change the data layout for any reasons, the code again will have to be updated. It would be more certain if you change it to something else... running a 3 level loops with multiple if is not something easy for me as you require to paste data in a different order... regarding this, I would only see using name range from Excel name range menu and use these names in the code, then you can add as many column you want, perform any change you want in the layout (as long as you don't delete a name or a column that has this name), the code should always work and it should even be faster if deal with many rows... Now I don't see any other way to do this, even if using more excel vba objects, selecting a range, criteria rows filter, hiding columns, copy visible cells like with Excel F5 shortcut key, at the end you still want to paste your data in a different position. The most simple thing you could have done as Don said, is filtering, and then you could record a macro that hides the columns you dont want and copy/paste one by one visible cells and stop recording macro. Everytime on, you can run the macro and it does the same as the code above. However, it has the same limit, you will not be able to change the layout anywhere in your file. Anyway, you didn't raise this issue, I just want to see if someone might know the best thing to do related to this. Pascal multiskillz <http://www.multiskills.elementfx.com/> -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
