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

why do i have to start running this macro in a sheet in which AL54
has a value & not any other sheet outside selected "range" sheets??
Would prefer it to work when any sheet is active - thxs



Sub Goalseekoffsetall()

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


..Cells(84, Range("AL54").Value + 4).Goalseek Goal:=.Range("AL84"),
ChangingCell:=.Cells(50, Range("AL54").Value + 4)

..Cells(85, Range("AL54").Value + 4).Goalseek Goal:=.Range("AL85"),
ChangingCell:=.Cells(51, Range("AL54").Value + 4)

..Cells(86, Range("AL54").Value + 4).Goalseek Goal:=.Range("AL86"),
ChangingCell:=.Cells(52, Range("AL54").Value + 4)

End If
End With

Next WS

End Sub

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

When your code is in a general module and you don't qualify the ranges, then
those unqualfied ranges refer to the activesheet.

So
Range("AL54").Value
is the same as:
Activesheet.Range("AL54").Value

So you can specify the sheet that contains that cell:

..Cells(84, worksheets("somesheetnamehere").Range("AL54").Valu e + 4)...

or

..Cells(84, .Range("AL54").Value + 4)....
if that AL54 should be picked up anew from each worksheet in the loop.

al wrote:

why do i have to start running this macro in a sheet in which AL54
has a value & not any other sheet outside selected "range" sheets??
Would prefer it to work when any sheet is active - thxs

Sub Goalseekoffsetall()

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

.Cells(84, Range("AL54").Value + 4).Goalseek Goal:=.Range("AL84"),
ChangingCell:=.Cells(50, Range("AL54").Value + 4)

.Cells(85, Range("AL54").Value + 4).Goalseek Goal:=.Range("AL85"),
ChangingCell:=.Cells(51, Range("AL54").Value + 4)

.Cells(86, Range("AL54").Value + 4).Goalseek Goal:=.Range("AL86"),
ChangingCell:=.Cells(52, Range("AL54").Value + 4)

End If
End With

Next WS

End Sub

--

Dave Peterson