How to repeat a code for selected sheets (or a contiguous range of sheets) in a Workbook?

D

Dmitry

Hello
How to repeat a code for selected sheets (or a contiguous range of sheets)
in a Workbook?
The code is:
Range("J16").Select
SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
ByChange:="$F$4:$I$12"
SolverSolve UserFinish:=True
 
G

Guest

For each ws in ActiveWorkbook.Sheets
ws.Range("J16").Select
SolverOk SetCell:="$J$16", MaxMinVal:=1, ValueOf:="0",
ByChange:="$F$4:$I$12"
SolverSolve UserFinish:=True

Next
 
D

Dmitry

Thanks Viquar for replying.
Does "For each ws in ActiveWorkbook.Sheets" repeats for all worksheets? I
wrote "How to repeat a code for selected sheets (or a contiguous range of
sheets) in a Workbook?", not all worksheets.
 
N

Norman Jones

Hi Dmitry,

Try something like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
For Each SH In ActiveWindow.SelectedSheets
With SH
Application.Goto .Range("J16")
'Your Solver code
End With
Next SH
End Sub
'<<=============
 
D

Dmitry

Thanks. I will try.
Norman Jones said:
Hi Dmitry,

Try something like:

'=============>>
Public Sub Tester()
Dim SH As Worksheet
For Each SH In ActiveWindow.SelectedSheets
With SH
Application.Goto .Range("J16")
'Your Solver code
End With
Next SH
End Sub
'<<=============
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Microsoft Visual Basic: Compile error: Sum or Function not defined 13
Solver in macro not work 1
VBA "sub or function not defined" 0
Solver iteration in VBA macro 2
SolverSolve userFinish:=True still displays the dialogue 0
Application.Quit Does not Close the Application 6
VBA code to RUN Solver mutiple times (on different target cells) 8
Solver 1

Top