my simple sorting macro doesn't work!

jem77brmr

New Member
Joined
Jul 19, 2005
Messages
11
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top