VBA Macro Dynamic Find Array, Copy and Paste only Certain Values based on Condition

Hi,

Im looking for assistance with my vba and hoping someone can assist with meeting my requirements below. It is the dynamic part of the code (identfying and only copying set rows) that i am struggling with.

In summary I have 2 files 'Weekly Sales (Identify Comp).xlsx' and 'Weekly Sales Report.xlsm'. The 'Weekly Sales (Identify Comp).xlsx' is the extract of data and with some tweaking i want to copy the data into the file 'Weekly Sales Report.xlsm'.

So getting into the detail, firstly you will see within the excel document titled 'Weekly Sales (Identify Comp).xlsx' that there is a long list of companies. For my output i dont need the entire list of companies all i need are the 'Competitor' companies. I need all the columns of the data. So thats the first step, secondly with the file being an aggregated file where the last reporting week will be added each week to the extract, i would also like to if possible be able to use an input (cell highlighted in 'yellow') in the 'Weekly Sales Report.xlsm' to set the week that i would like to extract.

Any help would be greatly appreciated.

Hey rjnc,

Please find attached the code i code come up with (had help with code snippets acquired over the years ). Check if this meets your need. I added sub Test in module 2 for your review.

Originally Posted by kay007

Hey rjnc,

Please find attached the code i code come up with (had help with code snippets acquired over the years ). Check if this meets your need. I added sub Test in module 2 for your review.

Thanks so much! This is exactly what i was after!!

Cool. Please mark the thread as resolved.

Originally Posted by kay007

Cool. Please mark the thread as resolved.

Sorry kay007 i have been having issues amending the code to suit the structure of the report. What i am needing is to paste the Competitor 1-3 information in separate tables. Where I have been having issues is with the length, i can get it to paste in the corresponding table, however when it is pasting the values it is only pasting the data to the length of Column A, instead of the end of the table. Do you know how to resolve? Many thanks.

Originally Posted by kay007

Cool. Please mark the thread as resolved.

Sorry kay007 i have been having issues amending the code to suit the structure of the report. What i am needing is to paste the Competitor 1-3 information in separate tables. Where I have been having issues is with the length, i can get it to paste in the corresponding table, however when it is pasting the values it is only pasting the data to the length of Column A, instead of the end of the table. Do you know how to resolve? Many thanks.