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!
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!