Looping through worksheets

**The Phil** · 01-17-2014, 02:18 PM

Hi,

I am trying to perform the same actions on all of the worksheets in my workbook. I have tried the following two codes but neither of them work:

Dim ws As Integer

For ws = 1 To Worksheets.Count
 Sheets(ws).Select
  Cells(4, 4) = RYear
  Range("A5") = "setdefault Period=" & RYear & "00:" & RYear & "13"
  Range("A6") = "setdefault Period_from=" & RYear & "00"
  Range("A7") = "setdefault Period_to=" & RYear & "13"
  Range("B16") = Range("D3") & Range("D4")
  Range("B17") = "Report run on " & Format(Date, "mmm dd, yyyy")
Next ws

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
 Cells(4, 4) = RYear
 Range("A5") = "setdefault Period=" & RYear & "00:" & RYear & "13"
 Range("A6") = "setdefault Period_from=" & RYear & "00"
 Range("A7") = "setdefault Period_to=" & RYear & "13"
 Range("B16") = Range("D3") & Range("D4")
 Range("B17") = "Report run on " & Format(Date, "mmm dd, yyyy")
Next

I tried that last one with "next" and "next ws".

Neither of these are making all of the changes that are specified. Any thoughts?

Thank you.

**Sean Thomas** · 01-17-2014, 02:22 PM

whats not working?
is there an error? if so where is the error occuring?

**judgeh59** · 01-17-2014, 02:22 PM

maybe try adding ws. to the beginning of cells and ranges...

**Norie** · 01-17-2014, 02:24 PM

Try this.

For ws = 1 To Worksheets.Count
    With Sheets(ws)
        .Cells(4, 4) = RYear
        .Range("A5") = "setdefault Period=" & RYear & "00:" & RYear & "13"
        .Range("A6") = "setdefault Period_from=" & RYear & "00"
        .Range("A7") = "setdefault Period_to=" & RYear & "13"
        .Range("B16") = Range("D3") & Range("D4")
        .Range("B17") = "Report run on " & Format(Date, "mmm dd, yyyy")
    End With
Next ws

**Sean Thomas** · 01-17-2014, 02:24 PM

what is Ryear? you dont have anything declared? is it a variable?
if it cant find it, it will error or ignore the code

**JOHN H. DAVIS** · 01-17-2014, 02:25 PM

Is RYear specified in each sheet or do you need to reference a value from a specific sheet?

Sub ThePhil()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
ws.Activate
 Cells(4, 4) = RYear
 Range("A5") = "setdefault Period=" & RYear & "00:" & RYear & "13"
 Range("A6") = "setdefault Period_from=" & RYear & "00"
 Range("A7") = "setdefault Period_to=" & RYear & "13"
 Range("B16") = Range("D3") & Range("D4")
 Range("B17") = "Report run on " & Format(Date, "mmm dd, yyyy")
Next ws
End Sub

**The Phil** · 01-17-2014, 03:06 PM

Thank you all for responding.

I added the "ws.activate" line and that fixed it.

My problem is that the cells weren't being updated on each sheet. Now it is working with that extra line of code.

Thanks!!

**Haikal_** · 01-19-2014, 10:21 AM

It is now working
i have added:
Ws.Activate

the final working code is


Sub RunMacroOnSpecificSheets()
Dim Ws As Worksheet, sName1 As String, sName2 As String

sName1 = "Weekday"
sName2 = "Weekdend"

For Each Ws In ThisWorkbook.Worksheets
Ws.Activate
    If Left(Ws.Name, Len(sName1)) = sName1 Or _
            Left(Ws.Name, Len(sName2)) = sName2 Then
        'Your Goes Code Here....
        
        Range("A1").Select
    ActiveCell.FormulaR1C1 = "Haikal"
        
        
    End If
Next Ws

End Sub

Looping through worksheets

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View