If cell C1 in each sheet contains a numerical value, the formula
=MATCH(2,1/FREQUENCY(MAX(Sheet1:Sheet8!C1),Sheet1:Sheet8!C1))
returns the number of the first sheet containing the maximum value.
If you can use sheet names like in your example with the same text string then just different index number it isn't that hard
="Sheet"&MATCH(MAX(Sheet1:Sheet8!C1),N(INDIRECT("'Sheet"&ROW(INDIRECT("1:8"))&"'!C1")),0)
entered with ctrl + shift & enter will return the sheet name that holds the MAX value, note that if there are multiple max values the first occurrence will be returned.
