Macro_error_in_2007_version_of_Excel

[pm_svefa]

I've run the following macrocode:

Range(urval).AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:W2"), CopyToRange:=Range("AG11:BH11"), Unique:= _
False

in many versions of Excel but suddenly in version 2007 it doesn't work
anymore. The funny thing is that it works in recording mode and it works in
normal GUI-usage, BUT not when trying to run the macro. ANYONE WHO KNOWS WHAT
TO DO ???

 

[Bob Flanagan]

Try setting Application.Screenupdating = true. I have seen setting it to
False prevents some code from running.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[pm_svefa]

Hi Bob ! Thks BUT unfortunately it didn't help :-(

 

[Jan Karel Pieterse]

Hi Pm,

Range(urval).AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:W2"), CopyToRange:=Range("AG11:BH11"), Unique:= _
False

Does this help:

Range(urval).ListObject.AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:W2"), CopyToRange:=Range("AG11:BH11"), Unique:= _
False


Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[pm_svefa]

Hi Jan ! No I get error "Object variable or With block variable not set".
I've tried to understand how to handle it without succeding. What should the
whole code look like including Dim and Set statements ??

FYI - earlier in code I have:
urval = "A11:AB" & ActiveCell.Row
The name of the worksheet is "Data"

 

[Jan Karel Pieterse]

Hi Pm,

FYI - earlier in code I have:
urval = "A11:AB" & ActiveCell.Row
The name of the worksheet is "Data"

Is it correct, that you are in fact copying the result on the same
sheet, next to the source table? It might be that Excel 2007 does not
like that. Make sure the destination of the advanced filter is below
the source data area, maybe that fixes the problem?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com