Macro to copy and paste filtered data in to a new sheet

[tomrzyz]

Hi there,

I'm currently in the process of building an excel sheet and am coming across a stumbling block which I need some assistance around. It involves the use of macros which are not something that I am very familiar with. Currently my use of macros has involved googling what I would want to do, finding something relevant and then through trial and error adapting it to my requirements.

This time I have been unable to do so successfully and my deadline for completion is nearing, hence why I am looking for your help.

Effectively what I would like to have would be a macro which would do the following:

Filter the cell AH9 on these values - Not Arranged, Still Within KPI or Not Arranged, Outside of KPI
Once filtered, copy the filtered data from the columns and rows B9-M9 (and to the end of the sheet, the amount of rows within the sheet will continue to grow on a day by day basis), and then paste them in to a new sheet named Collections Not Arranged.

Hopefully that is specific enough, thank you for your assistance and if you have any questions please let me know.

 

[easy2understandexcel]

Sticking with your tried and true method... The super easiest way to do what you want is to use an Advanced Filter... its 1 line of code!
Hit the record button to see what the code is...
create an advanced filter, with the 'copy to different range' option... and done!
it will look like this:

Range([put range here]).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range([criteria range here]), CopyToRange:=Range([paste it here]), Unique:=False

if you're not sure how advanced filter works, let me know and I can provide more info.

 

[tomrzyz]

Advanced filters worked perfectly. Thank you!

 

[easy2understandexcel]

You're welcome!