Not apply macro to every worksheet in activeworkbook
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 |
Not apply macro to every worksheet in activeworkbook
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) "G" wrote in message ... 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 |
Not apply macro to every worksheet in activeworkbook
Thanks Bob! That was a BIG help!
"Bob Phillips" wrote: 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) "G" wrote in message ... 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 |
All times are GMT +1. The time now is 02:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com