Goal seek for multiple sheets

[Biswas Uprety]

I have multiple sheets (30+ sheets) and I need to use goal seek analysis at once for all the sheets. Is it possible? Please help. It would really save me a lot of time.

 

[BSALV]

you have 1 parameter used in +30 sheets and the result (total?) of those +30 sheets is again summariced in 1 cell and this you want to do in 1 step ??
Is there a lineair relation between both or what ?

It seems difficult, just give it a try.
Otherwise, a small loop in VBA with 100 points in a specific range and write the result in a table.
That can give you a quick idea for a 2nd more detailed loop,

 

[Biswas Uprety]

Yes, the parameters and formulae in all the sheets are same. Only the values are different. I want to get the result at once either using goal seek or loop or by any other means. For example I can analyze a single worksheet using goal seek and then work on another and then another. But it takes too much time. So is there any way to analyze all the worksheets at once?

 

[BSALV]

I don't know what you really want at this moment, but suppose you have 100 sheets, al with a tiny difference and you want them all to provide with 1 value and know the result afterwards in the sheet "Summary".

Sub MultipleSheets()
     Dim Result(1 To 100)
     For i = 1 To 100
          With Sheets("MySheet" & i)
               .Range("A1").Value = i                           '----> give A1 of every sheet "MySheetx" a value
               Result(i) = .Range("Z1").Value                   '--->remember the value after calculation of that sheet
          End With
     Next
     Sheets("Summary").Range("A1").Resize(, 100).Value = Result     '--> write the 100 values in sheet Summary
End Sub

(I didn't check for errors)
So what is your goal ?

 

[Biswas Uprety]

I tried what u said above but it didn't work. My problem goes like this:
Suppose there are 100 students and i need to prepare their report. There's a single worksheet for every students calculating their individual marks among various subjects and percentage and the there is a common worksheet which includes practical marks and their attendance marks of all students. So for each student's worksheet I have taken a a reference from the common worksheet for their practical marks. I need to analyze how much marks a student must secure to pass (lets say 60) and if they obtain less than 60(lets say 45), how much extra marks should I give the student?

you have been really helpful and I am really hoping to solve soon, so please help

 

[BSALV]

This is a guess, i don't know the cells involved and the wanted result

Sub MultipleStudent()
     For i = 1 To 2
          With Sheets("Student" & i)
               naam = .Range("A1").Value                        'student's name
               ptns1 = .Range("K1").Value                       'student own points
               ptns2 = Sheets("Common").Range("P20").Value      'points for the whole class
               ptns3 = ptns1 + ptns2                            'total points
               ptns4 = Application.Max(0, 60 - ptns3)           'points still needed for 60
               s = s & Join(Array(naam, ptns1, ptns2, ptns3, ptns4), "; ") & vbLf     ' add data to tempory string
          End With
     Next
     MsgBox s, vbInformation, UCase("the points for the students")
End Sub

 

[Biswas Uprety]

I have attached a picture below. Maybe me being new to vba, I haven't been able to solve even after your so much help.
Like in the picture below, I have 280 other similar worksheets with only change in depth and width of foundation. There are also many "other" worksheets in the same workbook and values from these "other" worksheets have been referenced in the 280 worksheets. Maybe now we can come to a conclusion. I would really be grateful if it happens though I am already am for you previous help.
Thanking for your effort

 

[BSALV]

280 sheets with added value and 280 auxiliary sheets, overkill ?
What's the difference between sheet152 and sheet153 ?

How can excel know what kind of sheet it is ? The name of the sheet (starts or ends with ...), the content of cell A1, ...
The sheet with added value and the auxiliary sheet work as pairs together ?
You want to change the blue cell (address ???) and read the value of the 2 cells with the red arrows (addresses ???)

 

[Biswas Uprety]

No the auxiliary sheets are lot less because unlike in added value sheets, foundation are designed for all the depths in the same sheet. Added value sheets are different for different width of foundation and that's all. And added value sheets are dependent on auxiliary sheets but not the other way around. And analysis is to be done for only added value sheets. I have posted another picture to make it more clear. Maybe it will be clear. I have also added screenshots of my different worksheets names. Images s1 to s13 are screenshots of worksheets names.

 

[Biswas Uprety]

I cam up with a simple code which goes as (In my case):

Sub goal_Seek()
Dim row As Integer
row = 90
Do While Cells(row, 5) <> ""
Cells(row, "E").GoalSeek goal:=Cells(155, "F"), Changingcell:=Cells(row, "D")
row = row + 1
Loop
End Sub

Since my changing cell is also referenced and not a value(Value refenced from another sheet) while in calculation, it gives me message "Runtime error 1004" "Reference is not valid". Isn't there any way to use changing cell not as a value but rather as a reference.