Bom, como ninguém respondeu, vou citar minha solução, que não foi a mais funcional, porém, funcionou para o meu caso.
usei a função IF para ativar a planilha referente ao dia
If Day(Date) = 1 Then
ActiveWorkbook.Sheets("Dia1").Select
elseif Day(Date) = 2 then
....
Depois usei novamente a função if para escrever a mensagem da inputbox..
if (nome da inputbox) = 1 then
....
e para finalizar, ativei novamente a planilha 1, voltando assim ao ponto de origem.
Assim, você digitará o valor na inputbox, depois o código vai examinar que dia é hoje, e ativar a planilha referente ao dia, em seguida escrever na planilha correta o valor que voce escreveu, depois voltar a planilha original, para que voce digite outro valor. Essa é a fórmula geral, não vou passar a minha pois é bem específica para minha área de trabalho, que é lanchonete. Ficou um código um pouco extenso pois há muitos produtos, porém, funcionou perfeitamente