looping through worksheet & call a subroutine

[KNKN9]

Hi,

I have the code

 Dim ws As WorksheetFor Each ws In Workbooks("iteration_status.xlsm").Worksheets
If ws.Name <> "dd" And ws.Name <> "Summary" Then
Call CCIT6
End If
Next
End Sub

where CCIT6 is

Range("B7").End(xlToRight).AutoFilter Field:=4, Criteria1:="IT6"Range(Range(Range("B8"), Range("B8").Offset(0, 1)), Range(Range("B8"), Range("B8").Offset(0, 1)).End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheet1.Cells(Rows.Count, "E").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Range(Range("B8").End(xlToRight), Range("B8").End(xlToRight).End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheet1.Cells(Rows.Count, "G").End(xlUp).Offset(1, 0).PasteSpecial

However the code only works for the first worksheet. Can someone please help me spot where I am going wrong.

Many thanks!

 

[Norie]

You only refer to one sheet, Sheet1, in the code for CCIT6.

Which sheet(s) should the rest of the code refer to ?

 

[KNKN9]

Sheet(1) needs to be fixed as the is the sheet that information get pasted into..... I need for every sheet bar "Summary" and "dd" hence the code

[COLOR=#333333]or Each ws In Workbooks("iteration_status.xlsm").Worksheets
[/COLOR][COLOR=#333333]If ws.Name <> "dd" And ws.Name <> "Summary" Then[/COLOR]

 

[JackDanIce]

Replace above with following and try:

Sub LoopSheets()

    Dim x   As Long
    Const KeepSheet As String = "dd|Summary"
    
    Application.ScreenUpdating = False
    
    For x = 1 To Worksheets.Count
        With Sheets(x)
            If InStr(KeepSheet, .Name) = 0 Then Call CCIT6(Sheets(x))
        End With
    Next x
    
    Application.ScreenUpdating = True
     
End Sub


Sub CCIT6(ByRef wks As Worksheet)
    
    Dim LC  As Long
    Dim LR  As Long
    
    Application.ScreenUpdating = False
    
    With wks
        LC = Application.Max(2, .Cells(7, .Columns.Count).End(xlToLeft).Column)
        LR = Application.Max(8, .Cells(.Rows.Count, 2).End(xlUp).Row)
        
        With .Cells(7, 2).Resize(LR - 6, LC - 1)
            .AutoFilter field:=4, Criteria1:="IT6"
            .Offset(1).Resize(LR - 7, 2).SpecialCells(xlCellTypeVisible).Copy
            Sheet1.Cells(Rows.Count, 5).End(xlUp).Offset(1).PasteSpecial xlPasteValues
            .Offset(1).Resize(LR - 7).SpecialCells(xlCellTypeVisible).Copy
            Sheet1.Cells(Rows.Count, 7).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
    End With
    
    Application.CutCopyMode = False
        
End Sub

 

[jmacleary]

You need to explicitly select (or refer to in CCIT6) each worksheet in turn - simplest is to insert ws.Select
as follows:

 Dim ws As Worksheet
For Each ws In Workbooks("iteration_status.xlsm").Worksheets
If ws.Name <> "dd" And ws.Name <> "Summary" Then
ws.Select
Call CCIT6
End If
Next
End Sub

 

[KNKN9]

Thanks for the response !

 

[KNKN9]

This did the trick !

Thank you !