VBA Question

[davidlants]

Hi,

Here is my code, I just want VBA to run through all the worksheets in my workbook and complete the code in it but it won't work. The strange part is that it works fine if I take the code out of the worksheet loop and use it on one individual worksheet. Any help is greatly appreciated!!!!

Sub LoopThroughSheets()

Dim Current As Worksheet

For Each Current In Worksheets

Range("E4:AS4,E6:AS6,E8:AS8").Select
Selection.Style = "Percent"
Selection.NumberFormat = "0.0%"
Range("A1").Select
ActiveCell.FormulaR1C1 = "INDEX"
Range("A1").Select
With Selection.Font
.Name = "Calibri"
.Size = 6
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With
Activesheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="INDEX!A1", TextToDisplay:="INDEX"


Next


End Sub

 

[texasalynn]

try this (note: untested)

Sub LoopThroughSheets()
Dim Current As Worksheet
For Each Current In Worksheets
   With Current
    Range("E4:AS4,E6:AS6,E8:AS8").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "INDEX"
    Range("A1").Select
    With Selection.Font
        .Name = "Calibri"
        .Size = 6
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
End With
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="INDEX!A1", TextToDisplay:="INDEX"

Next
End Sub

 

[Joe4]

Welcome to the Board!

One way is to activate each sheet as you loop through them, i.e. after your "For..." statment, add this line:

For Each Current In Worksheets
    Current.Activate
    ...

 

[shg]

Or do it without selecting the sheets:

Sub LoopThroughSheets()
    Dim wks         As Worksheet
 
    For Each wks In Worksheets
        With wks.Range("E4:AS4,E6:AS6,E8:AS8")
            .Style = "Percent"
            .NumberFormat = "0.0%"
 
            With wks.Range("A1")
                .Value = "INDEX"
                wks.Hyperlinks.Add Anchor:=.Cells, Address:="", SubAddress:="INDEX!A1", TextToDisplay:="INDEX"
                With .Font
                    .Name = "Calibri"
                    .Size = 6
                    .ThemeColor = xlThemeColorLight1
                    .TintAndShade = 0
                    .ThemeFont = xlThemeFontMinor
                End With
            End With
        End With
    Next wks
End Sub

 

[davidlants]

Thanks shg,

This worked perfectly!

Thanks a lot!

David

 

[shg]

You're welcome, David.