Loop several sheets - part 3 - Dave Peterson - Goal seek
 

How can i goal seek on several sheets from 1 macro - pls correct macro
below which is not doing the work thxs

Sub Goalseekall()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = False 'Prevents the user from seeing
the screen
MinIndex = Worksheets("Pivot").Index
MaxIndex = Worksheets("End").Index

If MinIndex MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("Pivot").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index MinIndex _
And .Index < MaxIndex Then
'do the work


Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")
Range("Y85").Select
Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51")
Range("Y86").Select
Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52")

End If
End With

Next WS

End Sub

Loop several sheets - part 3 - Dave Peterson - Goal seek
 

You lost the leading dots again.

Try adding them to each of the Range()'s.

And delete the .select lines.

al wrote:

How can i goal seek on several sheets from 1 macro - pls correct macro
below which is not doing the work thxs

Sub Goalseekall()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = False 'Prevents the user from seeing
the screen
MinIndex = Worksheets("Pivot").Index
MaxIndex = Worksheets("End").Index

If MinIndex MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("Pivot").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index MinIndex _
And .Index < MaxIndex Then
'do the work

Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")
Range("Y85").Select
Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51")
Range("Y86").Select
Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52")

End If
End With

Next WS

End Sub

--

Dave Peterson

Loop several sheets - part 3 - Dave Peterson - Goal seek
 

On Jan 25, 8:24 pm, Dave Peterson wrote:
You lost the leading dots again.

Try adding them to each of the Range()'s.

And delete the .select lines.



al wrote:

How can i goal seek on several sheets from 1 macro - pls correct macro
below which is not doing the work thxs

Sub Goalseekall()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = False 'Prevents the user from seeing
the screen
MinIndex = Worksheets("Pivot").Index
MaxIndex = Worksheets("End").Index

If MinIndex MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("Pivot").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index MinIndex _
And .Index < MaxIndex Then
'do the work

Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")
Range("Y85").Select
Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51")
Range("Y86").Select
Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52")

End If
End With

Next WS

End Sub

--

Dave Peterson

Thxs - it seems to work -
Is the "with" necessary in this case??

Loop several sheets - part 3 - Dave Peterson - Goal seek
 

Is the "with" necessary in this case??

Necessary? No, but if you eliminate it, you would have to place its object
(the WS reference from the For Each statement) in front of every dotted
references. For example, where you now have this (using the With syntax)...

..Range("Y84").Goalseek Goal:=.Range("AL84"), ChangingCell:=.Range("Y50")

you would have to use this (if you remove the With block)...

WS.Range("Y84").Goalseek Goal:=WS.Range("AL84"),
ChangingCell:=WS.Range("Y50")

Notice the 3 ranges that picked up the WS reference. Going back to your
question... a With block is never "necessary", but it is a convenience; and,
in most cases, I believe it produces more efficient code as well.

--
Rick (MVP - Excel)

Loop several sheets - part 3 - Dave Peterson - Goal seek
 

Just to add to what Rick wrote, you could select the worksheet first, too.

ws.select
Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")

But that makes the code less friendly (in my opinion).

al wrote:

On Jan 25, 8:24 pm, Dave Peterson wrote:
You lost the leading dots again.

Try adding them to each of the Range()'s.

And delete the .select lines.



al wrote:

How can i goal seek on several sheets from 1 macro - pls correct macro
below which is not doing the work thxs

Sub Goalseekall()

Dim WS As Worksheet
Dim MinIndex As Long
Dim MaxIndex As Long
Dim RngToCopy As Range

ActiveWorkbook.PrecisionAsDisplayed = False
Application.ScreenUpdating = False 'Prevents the user from seeing
the screen
MinIndex = Worksheets("Pivot").Index
MaxIndex = Worksheets("End").Index

If MinIndex MaxIndex Then
'swap them
MinIndex = MaxIndex
MaxIndex = Worksheets("Pivot").Index
End If

For Each WS In ActiveWorkbook.Worksheets
With WS
If .Index MinIndex _
And .Index < MaxIndex Then
'do the work

Range("Y84").Goalseek Goal:=Range("AL84"), ChangingCell:=Range("Y50")
Range("Y85").Select
Range("Y85").Goalseek Goal:=Range("AL85"), ChangingCell:=Range("Y51")
Range("Y86").Select
Range("Y86").Goalseek Goal:=Range("AL86"), ChangingCell:=Range("Y52")

End If
End With

Next WS

End Sub

--

Dave Peterson

Thxs - it seems to work -
Is the "with" necessary in this case??

--

Dave Peterson