Chazal (Zohar Chadash, beginning of Shir HaShirim) teach that Moshe's name isn't mentioned in parashas Tetzaveh because of Moshe's request (32:32), כתבת אשר מספרך נא מחני אין ואם חטאתם תשא אם ועתה, "And now, if You would but forgive their sin! – but if not, erase me now from Your book that You have written." Hashem forgave the nation, but nevertheless, Moshe's request was answered, and the result is seen in parashas Tetzaveh.
The Nesivos Shalom zt'l asks, "Why was Moshe punished? Why isn't his name mentioned in Tetzaveh? He was moser nefesh for Bnei Yisrael, and this is his reward?! The answer is that he was punished because he said אין ואם, "but if not..." He said to Hashem, "If You don't answer my tefillos and save the nation, then erase my name from the Torah," implying that he wasn't sure that his tefillos for their atonement would be accepted, and that is why he was punished. We must believe that Hashem listens to every person's tefillah.
The Power of Tefillah
Tana d'Bei Eliyahu (ch.20) tells us that when Shaul captured Agag, he cried and moaned, "Woe is to me! Perhaps I won’t leave children in the world!" Hashem answered his tefillos, and he had descendants. That is how Haman was born.
Reb Chaim Kanievsky Zatzal says that this Chazal reveals the power of tefillah – even the tefillah of Agag the Amaleki, and even when his tefillos were for something negative, Hashem answered his tefillos. This shows us the power of tefillah. It can reach a level that is like grabbing onto Hakadosh Baruch Hu and telling Him that you won't let go until He does your will.
After this tefillah, Moshe descended from the mountain, reproved the nation, burned the egel, and took other steps to rectify their grave sin. Then Moshe returned to Har Sinai to daven again. He said (32:32), תשא אם ועתה כתבת אשר מספרך נא מחני אין ואם חטאתם, "And now if You would but forgive their sin! – but if not, erase me now from Your book that You have written."
Rashi explains that Moshe requested that his name be erased from the entire Torah, "So people won't say about me that I wasn't worthy to daven for the nation."
But, behold, Moshe was האדם מכל עניו, the humblest person who ever lived. Why did he think he was worthy of his tefillos to be accepted? Furthermore, due to his humility, why would he care if people ridiculed him and claimed he wasn't worthy of davening for the nation?
The Beis Aharon (Likutim 144:) answers that Hakadosh Baruch Hu listens to the tefillah of every person – regardless of his level. It was important to Moshe that Hashem answer his tefillah to prove that Hashem listens to the tefillos of even the lowest person. If his tefillos were not answered, people at low levels would lose their faith in prayer.
