I am new to macros and visual basic. I am trying to get the following to run
on all sheets. After searching I found what I thought would work, but it only
works on the active sheet and not all sheets. Please help.


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("A1:P317").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

I think you need to activate each sheet as you go something like this:

For Each ws In ActiveWorkbook.Worksheets
ws.activate
Cells.Find(What:="put options", After

Tom

Reposted in .programming.

trey1982 wrote:

I am new to macros and visual basic. I am trying to get the following to run
on all sheets. After searching I found what I thought would work, but it only
works on the active sheet and not all sheets. Please help.

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("A1:P317").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

On Mon, 6 Apr 2009 16:39:01 -0700, trey1982
wrote:

I am new to macros and visual basic. I am trying to get the following to run
on all sheets. After searching I found what I thought would work, but it only
works on the active sheet and not all sheets. Please help.


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("A1:P317").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


This macro runs by each worksheet. It is easy to see how it works, and
how you would need to make the changes needed to your macro. At least it
appears easy to me. (the 'THEN' remark on the tenth line belongs on line
nine as Usenet line lengths are limited)

Sub selprint()
Dim i As Integer
Dim currentsheet As Worksheet

For i = 1 To ActiveWorkbook.Worksheets.Count
Set currentsheet = ActiveWorkbook.Worksheets(i)
Worksheets(i).Activate
'Skip empty sheets and hidden sheets
If Application.CountA(currentsheet.Cells) < 0 And currentsheet.Visible
Then
'change the hard-coded cell here if not F52
If (Not IsNull(Range("F52"))) And (Range("F52").Value < 0) Then
'un-comment the next line when debugging completed
' ActiveSheet.PrintOut
'add comment at start of next line when debugging completed
ActiveSheet.PrintPreview
End If
End If
Next i
End Sub