Use the Excel Array Function and Nested Loops to Process Specific Sheets (File Link Included)

Hey Excellers!

Another ExcelPowerTip showing how you can use the Excel VBA Array Function together with Nested Loops to process specific worksheets to copy and paste into a summary sheet.

I have also made the VBA code dynamic so that it will only process worksheets that have no colour and will ignore any sheets that is coloured red. You can change the colour of the tab from red to no colour and the VBA Macro will then pick it up and also the other way around.

VBA Code included below for you.

Download the complete Excel workbook here:

Hope you find this tip useful and can apply it to your work.

LIKE - SUBSCRIBE - SHARE - COMMENT - ENJOY!

----------------------------------------------------------------------------------------------------------------------
Public Sub Use_Arrays_Function_with_Loop_Process_Specific_Sheets()
' Developed by ExcelPowerTips - 2024

Dim mySheet As Worksheet
Dim mySummSheet As Worksheet
Dim sheetNamesList As Variant
Dim idx As Integer

Application.ScreenUpdating = False
Set mySummSheet = Worksheets("TEAMS SUMMARY") 'Sheet0

' Put your list of sheet names in an Array Function
sheetNamesList = Array("TEAM 1", "TEAM 2", "TEAM 3", "TEAM 4", "TEAM 5", "TEAM 6", "TEAM 7", "TEAM 8")

Debug.Print "First Array List Index = " & LBound(sheetNamesList) ' shows the index of the First element of the Array (usually 0)
Debug.Print "Last Array List Index = " & UBound(sheetNamesList) ' shows the index of the Last element of the Array (this will be the number of elements - 1)

With mySummSheet
.Activate
.Range("B7:R200").ClearContents
End With

' Use a loop to look through all the sheet names and check to see if its in the Array List using
' another nested loop and if it is then process it in terms of copying the sales data to the summary sheet
sheetNamesList = Array("TEAM 1", "TEAM 2", "TEAM 3", "TEAM 4", "TEAM 5", "TEAM 6", "TEAM 7", "TEAM 8")
For Each mySheet In ThisWorkbook.Worksheets
For idx = LBound(sheetNamesList) To UBound(sheetNamesList)
If sheetNamesList(idx) = mySheet.Name And mySheet.Tab.Color NOT EQUAL vbRed Then
mySheet.Activate
mySheet.Range("B7:N7").Select
Selection.Copy
mySheet.Range("B2").Select
With mySummSheet
.Activate
nextRow = .Cells(Application.Rows.Count, "B").End(xlUp).Row + 1
.Range("B" & nextRow) = mySheet.Name
.Range("C" & nextRow).Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End If
Next idx
Next
mySummSheet.Range("B2").Select
Application.ScreenUpdating = True

End Sub
------------------------------------------------------------------------------------------------------------------------