Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 28th 05, 04:07 PM posted to microsoft.public.excel.setup
G
 
Posts: n/a
Default 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   Report Post  
Old November 28th 05, 05:01 PM posted to microsoft.public.excel.setup
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Old November 28th 05, 05:36 PM posted to microsoft.public.excel.setup
G
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy subtotalled cells to a new worksheet (in a macro), wi. LJB Excel Discussion (Misc queries) 2 June 23rd 05 02:00 AM
hide a worksheet so that a macro can still find it frendabrenda1 Excel Worksheet Functions 1 June 17th 05 04:30 PM
How do I apply footers to every worksheet (like Word)? I don' Zolli6769 Excel Worksheet Functions 0 April 19th 05 10:58 PM
Executing macro for all worksheet from a different worksheet Biti New Users to Excel 3 December 8th 04 10:05 AM
select worksheet to run macro Hidaya Excel Discussion (Misc queries) 5 December 1st 04 11:54 PM


All times are GMT +1. The time now is 01:33 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017