Macro insert new column in all worksheet

[tuytuy]

Hi i have a workbook with 21 worksheet.
i was trying to write a macro that would add a column at the beginning of each sheet with the name of the month entered by the user.
Here is my code, i don't know what is wrong with it but all i does is add 21 new column in sheet 1.

Sub Month ()
' ask user for month name

      Dim monthName As String
    monthName = InputBox(Prompt:="What Month", _
          Title:="Please entre name of the month", Default:="January 2013")


' insert a column
  Dim sht As Worksheet
    
    For Each sht In ActiveWorkbook.Worksheets
         
         Columns("A:A").Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
        Range("A1").Value = monthName
         
      Next sht
  
 End Sub

 

[zookeepertx]

I could be wrong, but I THINK that this:

Columns("A:A").Insert Shift:=xlToLeft,

should be this:

Columns("A:A").Insert Shift:=xlToRight,

 

[Useful]

Hi!
try this
(activate the sheets first like below)

Sub Month()
' ask user for month name


Dim monthName As String
monthName = InputBox(Prompt:="What Month", _
Title:="Please entre name of the month", Default:="January 2013")




' insert a column
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Worksheets
sht.Activate
Columns("A:A").Insert Shift:=xlToLeft, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Value = monthName

Next sht

End Sub

 

[tuytuy]

@zookeepertx Doesn't seem to work

 

[nuked]

Put this in a new module and amend as required.

Option Explicit

Sub AddColumn()

Dim sht As Worksheet
Dim result As String

result = InputBox("Please enter month...")

For Each sht In ActiveWorkbook.Worksheets
    sht.Range("A1").EntireColumn.Insert xlShiftToRight
    sht.Cells(1, 1) = result
Next sht

End Sub

 

[tuytuy]

Thank you so much for your help, worked perfectly.

 

[Rick Rothstein]

Put this in a new module and amend as required.

Option Explicit

Sub AddColumn()

Dim sht As Worksheet
Dim result As String

result = InputBox("Please enter month...")

For Each sht In ActiveWorkbook.Worksheets
    [B][COLOR=#a52a2a]sht.Range("A1").EntireColumn.Insert xlShiftToRight[/COLOR][/B]
    sht.Cells(1, 1) = result
Next sht

End Sub

You should be able to replace the highlighted line with this as well...

sht.Columns("A").Insert xlShiftToRight

It works for me.

 

[Useful]

when the entire column selected you'll neednt to use "xlShiftToRight" part