Copy multiple rows from a sheet into another sheet, based on a single criteria the header page! EXCELVBA?

Question

Trying to formulate the question itself was hard enough, so I hope this makes sense.

Based on a single cell "AAA" selected from a drop down list in the header page, I need it to search through a sheet "Rates" A1..W912 and then copy the rates that match criteria "AAA" into another sheet "Working_Rates", from row 2 until there are no more matching criteria.

Each time that the "AAA" is changed, say to "ZZZ", it should erase the previous rates in "Working_Rates" and replace with "ZZZ".

I hope that this makes sense and appreciated anyones/everyones assistance.

I have tried some examples from this and other websites, to no avail!

Many thanks,

Answer 1

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 2

Hans, thank you - however for some reason I am getting a #VALUE!

Could this be because the criteria cell is a drop down selection?

If so then I would need to find a way to copy that to another cell, however this would have to be converted to TEXT, and not the formulae itself - something I have also been looking to find out how to do!

Answer 3

Let's say that the drop-down is in cell B1 on a sheet named Header Sheet.

In A2 on the Working_Rates sheet:

=FILTER(Rates!A1:W912, Rates!A1:A912='Header Sheet'!B1, "- No Matches -"

Answer 4

Hi HansV

All matching cells (criteria) COULD be elsewhere in that worksheet, however I only need it to select based on the value (matching criteria) in column A.

Many thanks,

Answer 5

Are the matching cells to be found in a specific column? If so, which one?

Or can they be in any column in A1:W912?