Get macro to run on all sheets not just the active sheet

[trey1982]

I am new to macros and visual basic. I am trying to get what is found below
to run on all worksheets not just the active sheet which is happening now.

Thank you. (Sorry for the double post in Excel Worksheet Functions)


Sub stock1()
'
' stock1 Macro
'
' Keyboard Shortcut: Ctrl+y
'

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ActiveWorkbook.RefreshAll
Next
For Each ws In ActiveWorkbook.Worksheets
Cells.Find(What:="put options", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Range("A1317").Select
Selection.Cut
ActiveCell.Offset(-14, 12).Range("A1").Select
Cells.Find(What:="call options", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 17).Range("A1").Select
ActiveSheet.Paste
Next
End Sub

 

[Dave Peterson]

Option Explicit
Sub stock1()

Dim ws As Worksheet
Dim FoundPutCell As Range
Dim FoundCallCell As Range

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
With ws
Set FoundPutCell = .Cells.Find(What:="put options", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundPutCell Is Nothing Then
'not found on this sheet
Else
Set FoundCallCell = .Cells.Find(What:="Call options", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCallCell Is Nothing Then
'no call options found
Else
FoundPutCell.Range("A1317").Cut _
Destination:=FoundCallCell.Offset(0, 17)
End If
End If
End With
Next ws
End Sub

 

[trey1982]

Works like a charm. Thank you.

Option Explicit
Sub stock1()

Dim ws As Worksheet
Dim FoundPutCell As Range
Dim FoundCallCell As Range

ActiveWorkbook.RefreshAll

For Each ws In ActiveWorkbook.Worksheets
With ws
Set FoundPutCell = .Cells.Find(What:="put options", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundPutCell Is Nothing Then
'not found on this sheet
Else
Set FoundCallCell = .Cells.Find(What:="Call options", _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If FoundCallCell Is Nothing Then
'no call options found
Else
FoundPutCell.Range("A1317").Cut _
Destination:=FoundCallCell.Offset(0, 17)
End If
End If
End With
Next ws
End Sub