Excel VBA - Autofilter, copy selection, paste to new sheet

I have a table with source data in columns Sheet1!A6:R553.

In Sheet1!A3:R3, I have formulas that pull specific information out of my data table in Sheet1!A6:R553 that I want copied to a different sheet.

Column A acts as my project name column, while column B holds ID numbers. In my case, there are multiple ID numbers per project.

I am looking for a script to filter and loop through all the unique order numbers in Column B one by one, then copy cells A3:R3 to Sheet2!A12:R12 for as many rows as there are unique order numbers (i.e, add rows to the table).

Example: Assume there are 5 projects in my data sheet. I will filter the data using another macro to select Project_1. I would then like a command button to active a macro that will filter to the first order number in Column B, copy Sheet1!A3:R3 to Sheet2!A12:R12, then filter to the second order number in Project_1, and repeat the process. This should go on until all unique ID numbers have been filtered and looped through.

Hi Timlahaye,

could you please provide sample workbook with desire output it will help us to provide better solution

Hi Timlahaye,

could you please attached sample workbook here, as i do not have access to other sites.

you should of posted this in VBA section
you may have attracted more attention there

Put this code in Module 4
made many comments so you should be able to follow

Please Login or Register  to view this content.

re-looking at it
you may run into a little bit of problem with Ws3.column I if in circumstance there is old highway ID that doesnt get removed

so suggest you clear ws3.column I before pasting new data there either manually before running or inside the code itself

also may want to look at clearing the summary before running the code

besides that its an interesting setup you have there with the formulas in ws1 row 3

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

not a problem
glad to help

Thanks for the rep

not quite sure what your example is meant to do
maybe try this
http://analysistabs.com/vba-code/worksheet/m/calculate/

VBA Calculate Worksheet Method: In Specified Range