Advanced filter current.region why does it give runtime error 1004

IanPM

Board Regular
Joined
Dec 12, 2013
Messages
53
Why does below auto filter fail when CurrentRegion was added. What will be the correct code to fix the 1004-The extract name has a missing or invalid name. I need to have a variable region for the auto filter?

The code

Sub DATA()

' DATA Import macro
Sheets("ACC&WD").Range("B8").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("L10:T11"), CopyToRange:=Range("B15:J15"), Unique:= _
False

End Sub

Thanks Ian
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,082
Office Version
  1. 2019
Platform
  1. Windows
Hi,
is the worksheet protected? CurrentRegion property cannot be used on a protected sheet.

Dave
 

IanPM

Board Regular
Joined
Dec 12, 2013
Messages
53
The file was protected, but currently is not. The sheet is a new sheet & is not protected
 

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44
Could it be because your ranges don't match? I'm new to VBA so may be a stupid suggestion

10:11 to 15:15?
 

IanPM

Board Regular
Joined
Dec 12, 2013
Messages
53

ADVERTISEMENT

These ranges you are referring to are no the data ranges, but the criteria range L10:T11 & where to copy the auto filter to B15:J15. Th currentRegion refers to the data tat needs to copied into the auto filer. So the error relates to the data selection area ,which should start at Sheets ("ACC&WD") Cell B8.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows
Are there any values in the destination range B15:J15?
 

IanPM

Board Regular
Joined
Dec 12, 2013
Messages
53
Found the solution to adapt the data source tables & some other changes. What I need to know is how can I manually enter values into the filtered information & not loosing it?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,965
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top