loop for work sheets

tahoney

New Member
Joined
Nov 11, 2005
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
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.
Code:
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

Board Regular
Joined
Oct 5, 2005
Messages
175
For Each ws In ActiveWorkbook.Worksheets
'code to run goes here
Next ws
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
Another way; this example prints "Hi" in cell A1 of every sheet in the Active Workbook:
Code:
        Dim i As Long
        For i = 1 To Worksheets.Count
            Sheets(i).Select
            Range("A1") = "Hi"
        Next i
 

tahoney

New Member
Joined
Nov 11, 2005
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
How is the x value changing?

Is this all one macro?
Code:
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

New Member
Joined
Nov 11, 2005
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
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

New Member
Joined
Nov 11, 2005
Messages
7
it stops at
If wsDay.Name <> wsRte Then
should rte1 be a workbook instead of wksheet?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,275
Sorry typo there, forgot the Name for the rte worksheet.:oops:

It is a worksheet isn't it?:)
Code:
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.
 

Forum statistics

Threads
1,077,938
Messages
5,337,296
Members
399,138
Latest member
eurogator

Some videos you may like

This Week's Hot Topics

Top