Advanced filtering macro

S

Sandy

Hi

Can anyone tell me why this works:-

Sheets("Sheet1").Select
Range("A10").Select
Range("A10:V300").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:X2"), Unique:=False
Range("A10:W300").Select
Selection.Copy

Sheets("Search").Select
Range("H7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False

and this doesn't:-

Sheets("Sheet1").Range("A10:V300").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:X2"), Unique:=False
Range("A10:W30000").Copy

Sheets("Search").Range("H7").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sandy
 
B

Bernie Deitrick

Sandy,

When you don't select, you often need to fully qualify your ranges. This worked for me:

Sheets("Sheet1").Range("A10:V300").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Sheet1").Range("A1:X2"), Unique:=False
Sheets("Sheet1").Range("A10:W300").Copy

Sheets("Search").Range("H7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


HTH,
Bernie
MS Excel MVP
 
S

Sandy

Sorry - should read:-

Can anyone tell me why this works:-

Sheets("Sheet1").Select
Range("A10").Select
Range("A10:W300").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A1:X2"), Unique:=False
Range("A10:W300").Select
Selection.Copy

Sheets("Search").Select
Range("H7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False

and this doesn't:-

Sheets("Sheet1").Range("A10:V300").AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:X2"), Unique:=False
Range("A10:W300").Copy

Sheets("Search").Range("H7").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Sandy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top