PDA

View Full Version : Solved: Inserting & Naming New Columns in Multiple Worksheets



KoE
04-17-2009, 01:34 AM
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 :banghead:

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 :bow:

MikeBlackman
04-17-2009, 01:49 AM
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
04-17-2009, 02:09 AM
Mikey,

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

MikeBlackman
04-17-2009, 02:10 AM
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
04-17-2009, 02:37 AM
Mikey,

ya da man!!! :bow: :bow: :bow: