VBA to Insert column in multiple sheets

[Hellamint]

Good Morning all,

I have a workbook with multiple worksheets. Within each worksheet is a column heading named "CurrencyDesc" (located in a different column in each sheet, and that will always change). I want to find "CurrencyDesc" and insert a column to the right of it. I have part of the code, but it only applies it to the first worksheet.



ActiveWorkbook.Sheets.Select

Selection.EntireColumn.Select

Cells.Find("CurrencyDesc").Select

ActiveCell.EntireColumn.Offset(0, 1).Insert



I need it to apply the find and insert to ALL of the worksheets. What am I missing?

THANK YOU IN ADVANCE!!!

 

[Chantty]

Try this:-

for a = 1 to sheets.count
Sheets(a).Select

Selection.EntireColumn.Select

Cells.Find("CurrencyDesc").Select

ActiveCell.EntireColumn.Offset(0, 1).Insert

next

 

[Hellamint]

Cells.Find("CurrencyDesc").Select is now giving me a debug error.....

 

[Chantty]

You may get an error if there is no "CurrencyDesc" column in the worksheet.

Paste this in first line with in the macro:

on error resume next

 

[Datsmart]

Sub InsertColumnx()
Dim WS As Worksheet
    For Each WS In Worksheets
        On Error Resume Next
        WS.Cells.Find("CurrencyDesc").EntireColumn.Offset(0, 1).Insert
    Next WS
End Sub

 

[Hellamint]

Thank you so much Datsmart! Would you be so kind as to describe what each line in the code is doing for me?

MANY THANKS!!!

 

[Datsmart]

I hope this can help:

Sub InsertColumnx()
Dim WS As Worksheet
    
    'Cycle through each worksheet in workbook
    For Each WS In Worksheets
        
        'If FIND function finds nothing, go to Next WS
        On Error Resume Next
        
        'If FIND function finds the text, insert a new column "offset" one column
        WS.Cells.Find("CurrencyDesc").EntireColumn.Offset(0, 1).Insert
    
    'Return to above "For" on next worksheet
    Next WS
End Sub