Results 1 to 16 of 16

Loop through a Filtered List

Threaded View

  1. #1
    Registered User
    Join Date
    12-14-2009
    Location
    Bronx, NY
    MS-Off Ver
    Excel 2007
    Posts
    7

    Smile Loop through a Filtered List

    Hello,

    I am trying to write a macro that will let me loop through an auto-filtered list (column C in example file) . I want to filter for a criteria (in this case a network), copy the filtered data, paste it onto a new workbook and save it. Although I have been able to record a macro (see below) that can do this, I have to copy and paste this macro over and over again for the other networks. I would like to know if there is a macro that can loop through each criteria in an auto-filter list, copy and save the data into new workbooks for me. I've attached a sample workbook that should be helpful in understanding what I'm trying to do. Any help is much appreciated.

    Sub ATTD()
    '
    ' ATTD Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+A
    '
        ActiveSheet.Range("$B$2:$D$8").AutoFilter Field:=2, Criteria1:="A"
        Columns("A:D").Select
        Selection.Copy
        Workbooks.Add
        ActiveSheet.Paste
        Columns("A:A").ColumnWidth = 3.14
        Columns("D:D").EntireColumn.AutoFit
        Range("C9").Select
        Application.CutCopyMode = False
        ActiveWorkbook.SaveAs Filename:= _
            "\\Desktop\TEST\ATTD Network A.xls", _
            FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
        ActiveWindow.Close
        Range("B12").Select
        ActiveSheet.Range("$B$2:$D$8").AutoFilter Field:=2
        Range("A1").Select
    End Sub
    Attached Files Attached Files
    Last edited by EggHead; 12-21-2009 at 01:30 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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