Tools -> Script Editor, and paste in the following code

selectDay.js

var dayNumToName = [
  "Sunday",
  "Monday",
  "Tuesday",
  "Wednesday",
  "Thursday",
  "Friday",
  "Saturday"
];

function selectTabBasedOnDay() {
  var dow = (new Date()).getDay();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dayNumToName[dow]);
  if (sheet !== null)
    sheet.activate();
}

function onOpen() {
  selectTabBasedOnDay();
}