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

Hi
rngDatabaseRangeForEachSheet is a range object so replace

Range(rngDatabaseRangeForEachSheet)

with

rngDatabaseRangeForEachSheet

regards
Paul

On Dec 12, 2:29*pm, Jake wrote:
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

that was it; thanks Paul for the help.

" wrote:

Hi
rngDatabaseRangeForEachSheet is a range object so replace

Range(rngDatabaseRangeForEachSheet)

with

rngDatabaseRangeForEachSheet

regards
Paul

On Dec 12, 2:29 pm, Jake wrote:
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