Run macro on all sheets

  • Thread starter Thread starter trey1982
  • Start date Start date
T

trey1982

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.
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
Click to expand...
 
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
Click to expand...


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