For each, Next - 4 worksheets

K

Kevin

Hi

I have written some code that works ok. I need to do the same function on 4
different worksheets (the macro updates the 4 sheets in one go). What is a
simple way to write some code so it replicates the same function in the 4
sheets without entering the code 4 times (referring to a each sheet)
 
D

Don Guillett

As ALWAYS,post YOUR code for comments and suggestions.

for each ws in thisworkbook.worksheets
if ws. name="firstone" or ws.name="secondone" then
ws.do your thing
end if
next ws.
 
M

Mike

Maybe something like this
Sub eachSheet()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("A1").Value = "Do Your Code Here"
Next
End Sub
 
M

mikeaj72

Kevin;127070 said:
Hi

I have written some code that works ok. I need to do the same function
on 4
different worksheets (the macro updates the 4 sheets in one go). What
is a
simple way to write some code so it replicates the same function in the
4
sheets without entering the code 4 times (referring to a each sheet)
Click to expand...

Sub eachSheet()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range("A1").Value = "Do Your Code Here"
Next
End Sub
 
R

Rick Rothstein

Something like this maybe...

SheetNames = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4")
For Each SN in SheetNames
With Worksheets(SN)
'
' Do whatever you need to here. Remember to put a dot in front of
' each property/method call, such as .Range("A1"), so that it will
' refer to the worksheet that is the object of the With statement
'
End With
Next

where you would substitute your actual sheet names for the example ones I
used in the Array function call (remember to encase them in quote marks).
 
R

Rick Rothstein

I should point out, my code would be most useful if there were more than 4
worksheets in the workbook or if there could be more than 4 worksheets at
some point in the future. Otherwise, just iterate all the sheet as others
have posted.
 
Top