Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to copy subtotalled cells to a new worksheet (in a macro), wi. | Excel Discussion (Misc queries) | |||
hide a worksheet so that a macro can still find it | Excel Worksheet Functions | |||
How do I apply footers to every worksheet (like Word)? I don' | Excel Worksheet Functions | |||
Executing macro for all worksheet from a different worksheet | New Users to Excel | |||
select worksheet to run macro | Excel Discussion (Misc queries) |