my code runs by copying a specific range of data from multiple sheets that are available on the workbook. But I want to skip a sheet called "Data Recap" so that the code only runs for the other sheets only

what should I add to my code?

Sub Copy_Data()
    Dim ws As Worksheet, MasterSheet As Worksheet
    Dim originalDestinationCell As Range, nextDestCell As Range
    Dim firstGreyCell As Range, c As Range, e As Range, s As Range
    Dim lastRow As Long, firstRow As Long, colToCheckLast As Long, i As Long
    Dim isMain As Boolean
    
    Set MasterSheet = Sheets("Form Rekap")            'where you want to put the copied data
    Set originalDestinationCell = MasterSheet.Range("C6") 'the first cell the data will be copied to
    Set nextDestCell = originalDestinationCell.Offset(-1, 0)
    
    firstRow = 6
    colToCheckLast = 7
    
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = MasterSheet.Name Then
            Set firstGreyCell = ws.Range("C" & firstRow) 'Set first starting loop cell
            lastRow = ws.Cells(ws.Rows.Count, colToCheckLast).End(xlUp).Row
            isMain = True
            For i = firstRow To lastRow
                Set c = ws.Range("C" & i)
                Set e = ws.Range("E" & i)
                Set s = Nothing
                If isMain Then
                    If c.Interior.Color = firstGreyCell.Interior.Color Then
                        If Not IsEmpty(c) Then
                            Set s = c
                        Else
                            isMain = False
                        End If
                    End If
                Else
                    If c.Interior.Color = firstGreyCell.Interior.Color Then
                        If Not IsEmpty(c) Then
                            Set s = c
                        End If
                        isMain = True
                    Else
                        If Not IsEmpty(e) Then
                            Set s = e
                        End If
                    End If
                End If
                
                If Not s Is Nothing Then
                    Set nextDestCell = MasterSheet.Cells(nextDestCell.Row   1, originalDestinationCell.Column)
                    nextDestCell.Interior.Color = s.Interior.Color
                    nextDestCell.Value = s.Value
                End If
            Next
        End If
    Next ws
End Sub

CodePudding user response：

Few ways to do what you want:

Sub SkipSpecificWorksheet()
    Dim ws As Worksheet
    
    'Your version
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = MasterSheet.Name And Not ws.Name = "Data Recap" Then 'Add another condition
            'Do stuffs to the worksheet
        End If
    Next ws
    
    'Alternative
    'Same logic as above, just different syntax
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> MasterSheet.Name And ws.Name <> "Data Recap" Then
            'Do stuffs to the worksheet
        End If
    Next ws
    
    'Another alternative using Select Statement
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case MasterSheet.Name, "Data Recap" 'List of worksheet to skip
                
            Case Else
                'Do stuffs to the worksheet
        End Select
    Next ws
End Sub

CodePudding user response：

Process Worksheets With Exceptions

Option Explicit

Sub ProcessWorksheets()

    Const ExceptionsList As String = "Form Recap,Data Recap"
    
    Dim Exceptions() As String: Exceptions = Split(ExceptionsList, ",")

    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        If IsError(Application.Match(ws.Name, Exceptions, 0)) Then
            ' e.g.:
            Debug.Print ws.Name
        
        'Else ' is in the list; do nothing
        End If
    Next ws
    
End Sub