Loop through Specified Sheets and Run Code

Shezmu

New Member
Joined
May 22, 2014
Messages
2
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:

VBA Code:
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:

VBA Code:
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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(
 
Upvote 0
Thank you, something so simple yet completely changes how the code operates.

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

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top