+ Reply to Thread
Results 1 to 10 of 10

VBA/Macro - Advanced Filter Separating Data into Different Worksheets

  1. #1
    Registered User
    Join Date
    10-16-2011
    Location
    Chippenham, UK
    MS-Off Ver
    Excel 2003/2007
    Posts
    14

    VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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
    Attached Files Attached Files
    Last edited by Dame9255; 10-18-2011 at 05:13 AM.

  2. #2
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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.

  3. #3
    Registered User
    Join Date
    10-16-2011
    Location
    Chippenham, UK
    MS-Off Ver
    Excel 2003/2007
    Posts
    14

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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

  4. #4
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

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

  5. #5
    Registered User
    Join Date
    10-16-2011
    Location
    Chippenham, UK
    MS-Off Ver
    Excel 2003/2007
    Posts
    14

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

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

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-16-2011
    Location
    Chippenham, UK
    MS-Off Ver
    Excel 2003/2007
    Posts
    14

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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.

  8. #8
    Registered User
    Join Date
    10-16-2011
    Location
    Chippenham, UK
    MS-Off Ver
    Excel 2003/2007
    Posts
    14

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

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

    Glad it's working now!

  10. #10
    Registered User
    Join Date
    10-16-2011
    Location
    Chippenham, UK
    MS-Off Ver
    Excel 2003/2007
    Posts
    14

    Re: VBA/Macro - Advanced Filter Separating Data into Different Worksheets

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1