Results 1 to 6 of 6

Thread: my simple sorting macro doesn't work!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2005
    Location
    Houston, TX
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default my simple sorting macro doesn't work!

    I'm not good at this (very new) so I'm not shocked that it didn't work quite right, but it REALLY doesn't work right.

    What I would like it to do is filter a large sheet of information for anything that starts "NCC", then copy-paste that information into a sheet pre-labeled "coop agr". Instead, it pastes one cell with the wrong information. I used the "relative reference" button for selecting the sheet since all the files are different sizes, but it is confusing me terribly. Please help!

    Here's the code:

    Sub CooperativeAgreements()
    '
    ' CooperativeAgreements Macro
    ' Macro recorded 8/1/2005 by Derek Prather should separate Coop Agr from download
    '
    ' Keyboard Shortcut: Ctrl+q
    '
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="=*NCC**", Operator:=xlAnd
    ActiveCell.Cells.Select
    Selection.Copy
    Sheets("coop agr").Select
    ActiveCell.Cells.Select
    ActiveSheet.Paste
    ActiveCell.Cells.Select
    ActiveCell.Cells.EntireColumn.AutoFit
    End Sub

  2. #2
    Board Regular
    Join Date
    Dec 2004
    Location
    Austin, TX
    Posts
    174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What kind of error are you getting?

  3. #3
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jem,

    Try something like this (untested):
    Code:
    Sub CooperativeAgreements()
        Dim wsSrc As Worksheet, wsTgt As Worksheet
        Dim rngToCopy As Range
        
        With ThisWorkbook
            Set wsSrc = .Worksheets("Sheet1")
            Set wsTgt = .Worksheets("coop agr")
        End With
        
        With wsSrc
            Selection.AutoFilter Field:=5, Criteria1:="=*NCC**", Operator:=xlAnd
            Set rngToCopy = .AutoFilter.Range.SpecialCells(xlCellTypeVisible)
            rngToCopy.Copy
            wsTgt.Paste
        End With
        
    End Sub
    You should really include a defined range reference for teh range being filtered rather than relying upon Selection

    HTH
    Richie

  4. #4
    New Member
    Join Date
    Jul 2005
    Location
    Houston, TX
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hmmm... that code gets a range error right off the bat. Not sure, but is it because my download comes in as SAPBW_DOWNLOAD instead of "sheet1"?

    As for the error it was doing before, it did the filter alright, but then copied one cell that was not in my filtered range and placed it on the second sheet, where I wanted all visible cells copied over.

  5. #5
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jem,

    Yes, change "Sheet1" to the name of the sheet containing the data to be filtered (sorry, forgot to warn you about that assumption).
    Richie

  6. #6
    New Member
    Join Date
    Jul 2005
    Location
    Houston, TX
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    wow... it still won't get past that line and send me to the debugger right off the bat. I just don't understand how this stuff works. I'm going to try looking up range issues and see if I can find someway to make sure that I'm setting everything correct there...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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