Macro to extract records from a list

[cw1628]

Hi,

Can anyone tell me why I can't run this macro? Please tell me what the error is. Thanks

[Ikaabod]

The problem occurs when trying to AutoFilter. I'm not sure what it is you're trying to do with the AutoFilter, but if you're trying to make a copy of the data and drop it into a different WorkSheet this is not allowed by Excel. You can first drop it in the same Worksheet then do a cut/copy and paste into a different worksheet.

[cw1628]

Hey,

I am not too sure but I did find this on the website. http://www.meadinkent.co.uk/xlfilter.htm

It says,
"A macro can be used to automate the filtering process - identifying a List range, pre-programmed with the Criteria and the Copy to ranges. The results can be on a different worksheet than the original data."

[Ikaabod]

Actually, this is correct. I didn't realize this could be done, but I see how now. I believe you have to have the worksheet you are copying TO active in order to do this though. Also, if you are copying the data from a different worksheet make sure the autofilter is aware of this (i.e. range("A1:E5") is not the same thing as sheets("Sheet1").range("A1:E5") when the activesheet is "Sheet2").
So, run the macro with "Data" sheet active, then add this line:

Please Login or Register  to view this content.

right before the autofilter. Also, you will have to make sure you are pasting to a completely blank area. So even the headers: grade, surname, etc. cannot exist where the autofilter is to be copying to.

[cw1628]

hmm., do you think you can change it in the current file that I sent you. I'm not good with VB so i will need an example file to see. Thanks

[Ikaabod]

See attached. Here is the code:

Please Login or Register  to view this content.