Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Advanced filtering macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 270
Default Advanced filtering macro
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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 5,441
Default Advanced filtering macro
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


"Sandy" wrote in message
...
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



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 270
Default Advanced filtering macro
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

"Sandy" wrote in message
...
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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced Filtering by Date (or Macro) Help Alexis Excel Worksheet Functions 1 March 8th 06 05:09 PM
advanced filtering jiwolf Excel Worksheet Functions 2 October 19th 05 09:43 PM
Advanced filtering glassjo Excel Discussion (Misc queries) 1 September 25th 05 08:09 PM
advanced filtering in XLS Tina Excel Worksheet Functions 1 September 3rd 05 01:50 PM
Advanced Filtering Tony Excel Worksheet Functions 3 April 29th 05 11:48 AM


All times are GMT +1. The time now is 09:51 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement