Not apply macro to every worksheet in activeworkbook

[Guest]

Below this message are the codes that I created to apply a header in excel
spreadsheet (not the header in page setup). Unfortunately, the macro applies
the header multiple time in the one activesheet, instead of applying one per
each worksheet. Am I doing anything wrong?

Thanks,
G

Sub E_Insert_Headers()
'
Dim wks As Worksheet
Dim varInput As String

Application.ScreenUpdating = False

varInput = InputBox("Insert Date: (MM/DD/YY) Format")

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Rows("1:6").Select
Range("B1").Activate
Selection.Insert Shift:=xlDown
Range("k1").Select
Selection.FormulaR1C1 = varInput
With Selection
.NumberFormat = "[$-409]mmmm yyyy;@"
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 8
End With
Rows("7:7").Select
Range("B7").Activate
Selection.Rows.AutoFit
Range("G3").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Range("G4").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Next wks
End Sub

 

[Bob Phillips]

You have to use the wks identifier from the For loop

Sub E_Insert_Headers()
'
Dim wks As Worksheet
Dim varInput As String

Application.ScreenUpdating = False

varInput = InputBox("Insert Date: (MM/DD/YY) Format")

For Each wks In ActiveWorkbook.Worksheets
wks.Range("B1").Insert Shift:=xlDown
With wks.Range("K1")
.FormulaR1C1 = varInput
.NumberFormat = "[$-409]mmmm yyyy;@"
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 8
End With
wks.Rows("7:7").AutoFit
wks.Range("G3").HorizontalAlignment = xlLeft
wks.Range("G4").HorizontalAlignment = xlLeft
Next wks
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Thanks Bob! That was a BIG help!

You have to use the wks identifier from the For loop

Sub E_Insert_Headers()
'
Dim wks As Worksheet
Dim varInput As String

Application.ScreenUpdating = False

varInput = InputBox("Insert Date: (MM/DD/YY) Format")

For Each wks In ActiveWorkbook.Worksheets
wks.Range("B1").Insert Shift:=xlDown
With wks.Range("K1")
.FormulaR1C1 = varInput
.NumberFormat = "[$-409]mmmm yyyy;@"
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 8
End With
wks.Rows("7:7").AutoFit
wks.Range("G3").HorizontalAlignment = xlLeft
wks.Range("G4").HorizontalAlignment = xlLeft
Next wks
End Sub



--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Below this message are the codes that I created to apply a header in excel
spreadsheet (not the header in page setup). Unfortunately, the macro applies
the header multiple time in the one activesheet, instead of applying one per
each worksheet. Am I doing anything wrong?

Thanks,
G

Sub E_Insert_Headers()
'
Dim wks As Worksheet
Dim varInput As String

Application.ScreenUpdating = False

varInput = InputBox("Insert Date: (MM/DD/YY) Format")

For Each wks In ActiveWorkbook.Worksheets
On Error Resume Next
Rows("1:6").Select
Range("B1").Activate
Selection.Insert Shift:=xlDown
Range("k1").Select
Selection.FormulaR1C1 = varInput
With Selection
.NumberFormat = "[$-409]mmmm yyyy;@"
.Font.Name = "Arial"
.Font.FontStyle = "Bold"
.Font.Size = 8
End With
Rows("7:7").Select
Range("B7").Activate
Selection.Rows.AutoFit
Range("G3").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Range("G4").Select
With Selection
.HorizontalAlignment = xlLeft
End With
Next wks
End Sub