PDA

View Full Version : Selecting a range of sheets using crit in my Criteria



snowbounduk
01-27-2011, 08:46 AM
I have the following code which works fine.

Sub ACreateKeyProgrammeReports()

myCriteria = Range("A2:A5")
SourceSheetNames = Array("Slip No Issue", "Slip No Issue PP2", "Slip With Issue", "Slip To Left", "New MS", "Deleted", "Future Complete", "Past Incomplete", "Missing", "No_Pres", "Estimated_Duration", "Overdue_Tasks", "Dependencies", "Dependencies2")
For Each Crit In myCriteria
With Sheets(Crit)
' Delete Old Stuff First
Range(.Range("A1"), .Range("A1").End(xlDown)).EntireRow.Delete
' This will put the relevant line in from the summary tab for this programme
' Summary Line
Sheets("Key Summary").Range("A1").AutoFilter Field:=1, Criteria1:=Crit
Sheets("Key Summary").AutoFilter.Range.Copy .Range("A1")
Sheets("Key Summary").Range("A1").AutoFilter Field:=1
For Each SourceShtNme In SourceSheetNames
.Range("A1").End(xlDown).Offset(1).Value = SourceShtNme
Sheets(SourceShtNme).Range("A1").AutoFilter Field:=23, Criteria1:=Crit
Sheets(SourceShtNme).AutoFilter.Range.Copy .Range("A1").End(xlDown).Offset(1)
Sheets(SourceShtNme).Range("A1").AutoFilter Field:=23
Next SourceShtNme
End With
Next Crit
End Sub

However, I want to change the line With Sheets(Crit) to With Sheets("Key Best Network", "Key Capacity", "Key NTT", "Key NTP"). This code does not work, I've also tried using array and this does not work either.

I need to keep myCriteria = Range("A2:A5") but need a second range for the sheets I need to use.

Any help is much appreciated!

mdmackillop
01-27-2011, 01:01 PM
I don't believe you can autofilter on multiple sheets. You will need to loop as shown. Simple items such as this will function.
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Range("A3") = 3

snowbounduk
02-01-2011, 04:23 AM
The code as it stands does loop through multiple sheets. The original code has uses the same names for both criteria and sheets. All I am trying to change is the names of the sheets it pastes the info to.