I'm trying to run this code but only for specific worksheet names. the name is as follows "X_Score_" & CurrentDate where CurrentDate changes every time the loop runs. Right now it runs for all worksheets in the whole workbook which is quite messy.

Set wsMaster = ThisWorkbook.Worksheets("XXX_SCORE_TOTAL")
  
               RowTracker = 2
               flag = False
                
               For Each bs In ActiveWorkbook.Worksheets
                    
                    If UCase(bs.Name) <> "XXX_SCORE_TOTAL" Then
                        LastRow = bs.Cells(Rows.Count, "A").End(xlUp).Row
                        LastColumn = bs.Cells(1, Columns.Count).End(xlToLeft).Column
                        
                    End If
                    
                    bs.Range(bs.Cells(2, 1), bs.Cells(LastRow, LastColumn)).Copy wsMaster.Cells(RowTracker, 1)
                    
                    RowTracker = RowTracker   LastRow
                    
               Next bs

I tried to predifine something like Set MyCollection = ThisWorkbook.Worksheets("X_Score_" & CurrentDate) and then put in in the For loop like For Each bs In MyCollectionbut it didn't worked

CodePudding user response：

If the goal is to process all sheets in the workbook whose names start with "X_Score_", this can be done with Left():

Sub test1()
    Set wsMaster = ThisWorkbook.Worksheets("XXX_SCORE_TOTAL")
  
    RowTracker = 2
    flag = False
     
    For Each bs In ThisWorkbook.Worksheets ' or ActiveWorkbook?
        If Left(UCase(bs.Name), 8) = "X_SCORE_" Then
            
            LastRow = bs.Cells(Rows.Count, "A").End(xlUp).Row
            LastColumn = bs.Cells(1, Columns.Count).End(xlToLeft).Column
            
            Set Rng = bs.Range(bs.Cells(2, 1), bs.Cells(LastRow, LastColumn))
            Rng.Copy wsMaster.Cells(RowTracker, 1)
            
            RowTracker = RowTracker   Rng.Rows.Count ' shift RowTracker according to the number of rows copied 
        End If
    Next
End Sub