# loop for work sheets

#### tahoney

##### New Member
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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Norie

##### Well-known Member
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
Next ws``````

#### cfree36

##### Board Regular
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:
Code:
``````        Dim i As Long
For i = 1 To Worksheets.Count
Sheets(i).Select
Range("A1") = "Hi"
Next i``````

#### tahoney

##### New Member

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
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

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
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
it stops at
If wsDay.Name <> wsRte Then
should rte1 be a workbook instead of wksheet?

#### Norie

##### Well-known Member
Sorry typo there, forgot the Name for the rte worksheet. 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. Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,164,204
Messages
5,835,966
Members
430,398
Latest member
Wookiee_ ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back