VBA/Macro - Advanced Filter Separating Data into Different Worksheets

**Dame9255** · 10-17-2011, 04:33 AM

Like many others who visit this site I am a complete NOOB when it comes to VBA and Macros and I am hoping for some assistance on the attached file.

I would like to filter the data into their own worksheets based on the FM column, giving the worksheet the title of the FM Code.

I have reviewed a number of threads and have utilised a Macro posted by JBeaucaire, which works perfectly in Excel 2007, but I have to use it in Excel 2003, and this is where I get stuck. This Macro is not contained within the attachment.

Any assistance would be gratefully received.

Damien

**arlu1201** · 10-17-2011, 05:58 AM

Try this code -

Please Login or Register  to view this content.

Just ensure that you delete / hide the total line or else it will appear in sheets that have just 1 record.

**Dame9255** · 10-17-2011, 10:53 AM

Hi and thanks for your quick response.

After checking it on Excel 2007 and seeing that it works great I transferred it to Excel 2003. For a variety of reasons I had to type the code into Excel 2003. However on trying to use the code it stalls on the following (just over halfway down the code):

ActiveSheet.Range("$A$1:$S$" & 1row).AutoFilter field:=1, Criterial:=tabname

It doesn't seem to like the [1row] for some reason.

Any chance you could advise?

Many thanks

Damien

**arlu1201** · 10-17-2011, 01:58 PM

It doesnt like the 1row because its not 1row but lrow. (thats Lrow)...

**Dame9255** · 10-17-2011, 02:49 PM

thanks for clarifying. I will try it out tomorrow when I get back in to work.

**JBeaucaire** · 10-17-2011, 02:57 PM

Dame9225, I took the original ParseItems macro from the published site and edited only these two lines of code:

Please Login or Register  to view this content.

And it ran normally. What problems were you having? The macro was written on Excel2003.

**Dame9255** · 10-17-2011, 03:24 PM

Hi,

I don't think I was using the the macro you have inserted into the spreadsheet

The one I had started:

[Option Explicit

Sub ParseSiteData()
'JBeaucaire (11/11/2009)
'Based on column A, data is filtered to individual sheets
'Creates sheets and sorts alphabetically in workbook

I will try this one out tomorrow as well. Thank you so much. Up until recently I didn't even realise there was such a thing as VBA!! How mad is that

Damien

P.S. I will let you know how I get on with both bits of code.

**Dame9255** · 10-18-2011, 05:12 AM

I've managed to get a macro to work based on JBeaucaire's. this little exercise has taught me that it's very difficult to type everything up correctly when you transfer it to another system

Thanks to both of you guys for your help.

Damien

**JBeaucaire** · 10-18-2011, 10:50 AM

Why type? Cut and paste into a text file, transfer via email or thumb drive.

Glad it's working now!

**Dame9255** · 10-18-2011, 03:51 PM

You'd think it would be that easy but the thumb drive isn't working and the network I needed it on is internal with no external access.

Thanks again, this is a great site which I'm no doubt going to be using a lot of in the future.