setting advanced filter

[Jake]

Hello,
I would like to filter each sheet in a Data workbook based on Criteria in a
Main workbook, which contains code. Partial code as follows:

Sub CreateNewReport()
Dim wksData As Worksheet 'for the period data file
Dim rngCriteria As Range 'contains Criteria sheet on Main workbook
dim rngSingleCell as Range
Dim rngDatabaseRangeForEachSheet As Range

With Workbooks("Main")
.Activate
Set rngCriteria = Range("inpCriteria") 'Criteria range Main sheet,
labels and criteria cells
End With

Application.Workbooks("Data").Activate
For Each wksData In Workbooks("Data").Worksheets
Set rngDatabaseRangeForEachSheet = wksData.Cells(1,
1).CurrentRegion 'used as database range in filter

Range(rngDatabaseRangeForEachSheet).AdvancedFilter
Action:=xlFilterInPlace, CriteriaRange:= _
Range (rngCriteria), Unique:=False

This creates error 1004, Method Range of object_Global failed.
Something is wrong with the variables used in the Advanced Filter. I've
used similar code logic before with success, but I can't resolve this for
some reason.

Thanks for any help,
Jake

 

[paul.robinson]

Hi
rngDatabaseRangeForEachSheet is a range object so replace

Range(rngDatabaseRangeForEachSheet)

with

rngDatabaseRangeForEachSheet

regards
Paul

 

[Jake]

that was it; thanks Paul for the help.

Hi
rngDatabaseRangeForEachSheet is a range object so replace

Range(rngDatabaseRangeForEachSheet)

with

rngDatabaseRangeForEachSheet

regards
Paul