loop for work sheets

[tahoney]

hello everyone. i am looking for a loop for work sheets in a workbook.
i have a workbook ,rte1, that has 5 worksheets in it that are called tue,wed,thu,fri,sat. how can i make a macro that will perform a task on the first worksheet and then perform it on the next and so on. i have a macro now that does this but i access each sheet with its own code, which is the same, so the code is really long and redundant

 

[Norie]

Can you post the current code?

Is it the same for each worksheet?

How are you currently running it?

To loop through all worksheets you can try this.

Dim wb As Workbook
Dim ws As Worksheet
      
     Set wb = ActiveWorkbook
     
     For Each ws in wb.Worksheets
          ' do something with worksheet ws
          ' eg run your macro
     Next ws

 

[cfree36]

For Each ws In ActiveWorkbook.Worksheets
'code to run goes here
Next ws

 

[Joe4]

Another way; this example prints "Hi" in cell A1 of every sheet in the Active Workbook:

        Dim i As Long
        For i = 1 To Worksheets.Count
            Sheets(i).Select
            Range("A1") = "Hi"
        Next i

 

[tahoney]

okay here is a portion
Dim x As Integer, rng As Range
Dim y As Integer
Dim i As Integer
x = 5
y = 3
For i = 1 To 5

Sheets("rte1 ").Select
Range("B" & x & ":S" & x).Select
Selection.Copy
Sheets("tue").Select
Range("B" & y).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
x = x + 17
y = y + 1
Next
For y = 9 To 21 Step 6

Range("B3:S7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B" & y).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next
x = 6
y = 3
For i = 1 To 5

Sheets("rte1 ").Select
Range("B" & x & ":S" & x).Select
Selection.Copy
Sheets("wed").Select
Range("B" & y).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
x = x + 17
y = y + 1
Next
For y = 9 To 21 Step 6

Range("B3:S7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B" & y).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Next
x = 7
y = 3
For i = 1 To 5
as you can see the only thing that changes in each is the worksheet name and value of x

 

[Norie]

How is the x value changing?

Is this all one macro?

Dim wsDay As Worksheet
Dim wsRte As Worksheet
Dim x As Integer, rng As Range
Dim y As Integer
Dim i As Integer

y = 3

Set wsRte = Sheets("rte1 ")

For Each wsDay In Worksheets
    If wsDay.Name <> wsRte Then
        Select Case wsDay.Name
            Case "tue"
                x = 5
            Case "wed"
                x = 6
            ' Add more cases for different x values/sheet names
        End Select
        
        For i = 1 To 5

            wsRte.Range("B" & x & ":S" & x).Copy
            wsDay.Range("B" & y).PasteSpecial Paste:=xlValues
            x = x + 17
            y = y + 1
            
        Next i
        
        For y = 9 To 21 Step 6

            wsDay.Range("B3:S7").Copy
            wsDay.Range("B" & y).PasteSpecial Paste:=xlValues
            
        Next y
    End If
Next wsDay

 

[tahoney]

x changes by 17 in the macro then gets reset to 1 more than the original x x=5 for tues then x=6 for wed and so on

 

[Norie]

Did you try the code I posted?

It's not tested but I think it should at least give you some ideas.

There is one problem with it though, you'll need to reset the value of y after each loop.

Since you are reusing it in the second loop it's goinf to end up with the wrong value.

You should probably try and not reuse variables like that.

 

[tahoney]

it stops at
If wsDay.Name <> wsRte Then
should rte1 be a workbook instead of wksheet?

 

[Norie]

Sorry typo there, forgot the Name for the rte worksheet.

It is a worksheet isn't it?

If wsDay.Name <> wsRte.Name Then

This piece of code is so the code isn't run on the rte worksheet.

Please remember this code hasn't been tested, I don't have the same setup as you and don't actually know the purpose of the code. The code I posted is really just to try and illustrate some concepts and give you some ideas.