How to reference multiple sheets?

[bradlydraper]

I have pretty much a giant dictionary within my code, which recognizes common acronyms and turns them into spelled out words.


Take for example

Dim Ws As Worksheet
Set Ws = Worksheets("Sheet 2")

With ws
Cells.Replace What:=" m ", Replacement:="male", LookAt:=xlPart, SearchOrder:= xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=" f ", Replacement:="female", LookAt:=xlPart, SearchOrder:= xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End With

I have about 1200 lines of this, so taking the route of just writing the sheet at the start of the line is not a route that I would like to take


So what I want it to do now is for this to work on multiple worksheets. As you can see with the code above, it only changes these words for Sheet 2. I want it to change the words for Sheet 3 as well. I've tried things like

Dim sh2 as Worksheet
Dim sh3 as Worksheet

Set sh2 = ActiveWorkbook.Sheets("Sheet 2")
Set sh3 = ActiveWorkbook.Sheets("Sheet 3")

With Sh2 and Sh3
""
End With

And I've also tried just typing "With Sheets("Sheet 2") And ("Sheet 3") at the top, but I'm definitely not an expert with this and can't seem to figure it out

Any help is really appreciated

 

[scott_n_phnx]

You can try something like this:

Dim ws As Worksheet
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"))
	
With ws
	Cells.Replace What:=" m ", Replacement:="male", LookAt:=xlPart, SearchOrder:= xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
	Cells.Replace What:=" f ", Replacement:="female", LookAt:=xlPart, SearchOrder:= xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End With

Next ws

 

[bradlydraper]

This worked great, thank you for the help