Macro - Advanced Filter

I hope you can help!

I have a long list of data, with a range of department codes associated to the data. My workbook has a sheet containing the mass of data, and several other sheets containing differing groups of department codes. These groups of department codes are given/defined a name to make referencing them easier. Thus, I can quickly and easily advanced filter my main list of data, specify the different names under the criteria, and amend all the associated data linked to that department code in one go.

After I select each group through advanced filter, I put a letter into the column B for each of the rows - the letter varies according to the group chosen. Doing this manually basically means putting the letter into the top of the newly filtered rows, and copy/pasting it to the rows below.

However, I've tried to get a macro do to the same, to remove the last job. My macro can filter the data using the names fine, but it can't put the letter into all of the filtered rows. The problem is, I want it to start from the top of the filtered list, put in a letter, and then copy it to all of the filtered rows. However, Excel, as far as I can see, has no way of saying "the first of the filtered cells" - it always wants some kind of direct reference. It won't always be the same, as the top could be B2 for the first criteria, but then it won't be for the second, third, fourth etc...

Is there any way around this? If you're able to help then I'd really appreciate it!

It's very hard for anyone to solve a problem like this in the abstract, without being able to visualise your data. So I suggest you attach a workbook with a small representative sample of your data and desired results.

Thanks for your reply Stephen, I hope I'm able to explain better!

Example1.xls is attached, and shows some of the data, together with two additional sheets detailing certain departments. Both sets of departments are defined as names (codeY and codeZ) and as such if you do an advanced filter on the data, with the criteria of either, it returns all the appropriate rows. This is correct!

Example2.xls contains a macro, which is to be run when in the data sheet. This performs an advanced filter using criteria codeZ, which works fine. It should then attempt to paste the letter Z into the top row of the filtered results in the Code column, and then select the remaining rows and copy down to them. Thus, all of the departments that are code Z automatically have the Z put in the Code field!

However, the macro code doesn't like to be vague, which is causing a problem. I want the macro to say, after selecting the criteria, "go to the top of the selected criteria, and click on whatever cell that is within the Code column, put in a Z, copy and paste to all other cells". However, Excel interprets this as "go to E2, put in a Z, copy and paste to all other cells". This workbook will be taking lots of different data, and E2 won't always be a code Z, just like it won't always be a Y or any other code. However, Excel appears to want to force it to be a specific cell reference, rather than just the top of the filtered results. Can you help?

Please Login or Register  to view this content.

Example3.xls shows this issue. In this workbook I've simply added a line at row 2 with new data, highlighted red. Try to run the macro again, then show all results, and you'll see that it's given the new row a code Z, even though it doesn't match the criteria, simply because it is row 2...!

I'm sure there's a better way than this, but this code works on your example 3 file and will work for any number of rows.

Please Login or Register  to view this content.

Here is another method. It may not work if your actual codes are not all of the form "Code " plus a letter.

Please Login or Register  to view this content.

Thank you very much for your responses - I'll give them a whirl