Solved: Inserting & Naming New Columns in Multiple Worksheets

[KoE]

Guys,

I'm trying to insert and label new columns in multiple worksheets, but the code inserts and labels the new columns in an active & unselected sheet

the code is as follows:

Sub Insert_Add_Col_To_Reflect_Transfer_Orders()
'
' Insert_Add_Col_To_Reflect_Transfer_Orders Macro
' insert additional columns to reflect transfer orders & their dates; up to 5 transfers
'
' Keyboard Shortcut: Ctrl+z
'
Application.ScreenUpdating = False
Dim sh As Worksheet
On Error GoTo 0
Application.DisplayAlerts = True
 
For Each sh In ActiveWorkbook.Sheets(Array("johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", "perak", "perlis", "ibu pejabat", "wp kl"))
 With sh
    Range("V1:AE1").Value = Array("Penempatan Pertama", "Tarikh", "Penempatan Kedua", "Tarikh", "Penempatan Ketiga", "Tarikh", "Penempatan Keempat", "Tarikh", "Penempatan Kelima", "Tarikh")
 End With
 
Application.ScreenUpdating = True
 
Next
 
End Sub

Would be mighy glad if someone can point to my mistakes

[MikeBlackman]

Hi,

Maybe try;

Sub Insert_Add_Col_To_Reflect_Transfer_Orders()
 
' Keyboard Shortcut: Ctrl+z
 
Application.ScreenUpdating = False
 
For i = 1 To Sheets.count
    Select Case Sheets(i).Name
        Case "johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", _
                "perak", "perlis", "ibu pejabat", "wp kl"
            Range("V1:AE1") = Array("Penempatan Pertama", "Tarikh", "Penempatan Kedua", "Tarikh", "Penempatan Ketiga", "Tarikh", _
                "Penempatan Keempat", "Tarikh", "Penempatan Kelima", "Tarikh")
    End Select
Next i
 
End Sub

[KoE]

Mikey,

thanks for a fast response but it will only insert and name new columns in active sheet and not the specified sheets :-(

[MikeBlackman]

Oops, sorry;

Sub Insert_Add_Col_To_Reflect_Transfer_Orders()
 
' Keyboard Shortcut: Ctrl+z
 
Application.ScreenUpdating = False
 
For i = 1 To Sheets.count
    Select Case Sheets(i).Name
        Case "johor", "pulau pinang", "sabah", "sarawak", "selangor", "terengganu", "kedah", "kelantan", "melaka", "negeri sembilan", "pahang", _
                "perak", "perlis", "ibu pejabat", "wp kl"
            Sheets(i).Range("V1:AE1") = Array("Penempatan Pertama", "Tarikh", "Penempatan Kedua", "Tarikh", "Penempatan Ketiga", "Tarikh", _
                "Penempatan Keempat", "Tarikh", "Penempatan Kelima", "Tarikh")
    End Select
Next i
 
End Sub

[KoE]

Mikey,

ya da man!!!