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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
7,311
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
76,343
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,596
Messages
5,832,640
Members
430,150
Latest member
amitk1

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
Top