I am having trouble getting variable to work in this procedure. When I hardcode the ranges, it runs fine. The two variables I am having trouble with are 1.) defining the last row of the filter outSh Range, and 2.) defining the last row of the filter range.
Any help will be greatly appreciated of if you see any other issues that could improve the code.
Thanks
Mike
Any help will be greatly appreciated of if you see any other issues that could improve the code.
Thanks
Mike
Code:
'Set Filter in Place
Set outsh = Worksheets("FilterCriteria")
For Each ce In outsh.Range("BN3", outsh.Cells(Rows.Count), 66).End(xlUp)) 'BN58
For i = 1 To 10 ' Set i counter to the number of Data Sheets in Workbook
ToRow = Worksheets("Report1").Range("A65536").End(xlUp).Row + 1 ' ToRow resets to new row after each loop
With Worksheets("Data" & i)
On Error Resume Next
LastRow = .Range("A65536").End(xlUp).Row
.Range("AO1").Value = "Resource" 'Criteria Range copied into Data sheets
.Range("AO2").Value = ce.Value
.Range("AP1").Value = "Date"
.Range("AQ1").Value = "Date"
.Range("AP2").Value = Worksheets("FilterCriteria").Cells(3, 3)
.Range("AQ2").Value = Worksheets("FilterCriteria").Cells(3, 4)
.Range("A4:AN" & LastRow).AdvancedFilter Action:=xlFilterInPlace, criteriarange:=.Range("AO1:AQ2") ', copytorange:=outsh.Cells(Rows.Count, ce.Column).End(xlUp).Offset(1, 0)
.Range("AO1:AQ2").ClearContents
End With
Next i
Copy_Rng 'Procedure below
'Format_Print 'Procedure below
Next ce
End Sub