Loop through Specified Sheets and Run Code

[Shezmu]

I've created some code that works on individual sheets when I'm on them, but I would like to run this across specific sheets in the file. Have tried some code to loop through using sheets named in an array but not working as intended.

VBA code I want to run on each specified sheet:

Sub AddColumn()

Dim cl As Range
    
'Finds Cell with Movement in name
        Set cl = Cells.Find(What:="Movement", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
'Moves 1 column to the left from found column and inserts new column
           cl.Offset(0, -1).EntireColumn.Insert Shift:=xlToRight
        
'Copies contents of column to new column
            cl.Offset(0, -1).EntireColumn.Copy
            cl.Offset(0, -2).EntireColumn.PasteSpecial xlPasteValues
            
'Copies formula for titles
            cl.Offset(, -1).Copy
            cl.Offset(, -2).PasteSpecial xlPasteFormulas

End Sub

Loop code I've tried but not working as intended:

Sub PlacementLoop()

Dim WshtNames As Variant
Dim WshtNameCrnt As Variant
   
   WshtNames = Array("1156 Exp", "2257 Exp", "2792 Exp", "3052 Exp", "2257 Income", "2792 Income")


    For Each WshtNameCrnt In WshtNames
    
    With Worksheets(WshtNameCrnt)
    
Dim cl As Range
    
'Finds Cell with Movement in name
        Set cl = Cells.Find(What:="Movement", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
'Moves 1 column to the left to found column and inserts new column
           cl.Offset(0, -1).EntireColumn.Insert Shift:=xlToRight
        
'Copies contents of latest forecast to new column
            cl.Offset(0, -1).EntireColumn.Copy
            cl.Offset(0, -2).EntireColumn.PasteSpecial xlPasteValues
            
'Copies formula for titles
            cl.Offset(, -1).Copy
            cl.Offset(, -2).PasteSpecial xlPasteFormulas
End With
  Next WshtNameCrnt

End Sub

Would also like to run this code in similar files but may have less specified sheets in it, so the array of named sheets would need to be different, was wondering if there is a way to use the array to pull the sheet names from a group of cells/named range on a sheet in the file.

It's been a few years since I last used VBA regularly trying to get back in to it.

 

[John_w]

Add a dot "." in front of Cells.Find to refer to the With Worksheets object, rather than the active sheet, like this: Set cl = .Cells.Find(

 

[Shezmu]

Thank you, something so simple yet completely changes how the code operates.

Have figured out using a named range for the array as well.