The Sforno says that Hashem wanted Moshe to see the Land of Canaan in order to give it his blessing. Hashem then told Moshe to accept his death as atonement for having acted faithlessly, and in this way he will be gathered in the bond of life (The World to Come) with those who are as fitting and proper as he is. (Click here for Hebrew text.)
Moshe lost the privilege of entering the Land of Israel due to the episode of the hitting of the rock. On some minuscule level, which is a whole dvar Torah in of itself which the Torah testifies as not sanctifying G-D’s name, Moshe, in effect, profaned Hashem’s Holy name – for which he was punished.
The gemara in Yoma 86a quotes a discussion: “Rebbe Masia ben Cheresh asked Rebbe Elazar ben Azariah in Rome, ‘Is it true that there are 4 types of atonements that Rebbe Yishmael expounds upon?’ He said, ‘It is 3 and repentance has to come with each one of them. If one overlooks doing a positive mitzvah and repents, he does not leave where he is without being forgiven… If one transgresses a negative mitzvah and repented, the repentance is in limbo until Yom Kippur brings atonement… If one transgresses a sin deserving of kares (spiritually cut off from the nation) or a sin which warrants the death penalty but repents, then repentance and Yom Kippur are in limbo and suffering cleans away the sin… But a person who has profaned Hashem’s Name (chilul Hashem) then it is not enough with repentance to be in limbo, neither Yom Kippur to be an atonement, or suffering to cleanse the sin rather they are all in limbo and death cleanses the sin.
Moshe, on his own unique level, in a very subtle way, profaned Hashem’s Name. By doing so he lost the share, he originally deserved in the World to Come. But that is only if he does not atone for his sins. It would seem from this Sforno that even though the Torah testifies about Moshe that “Never again has there arisen in Israel a prophet like Moshe, whom Hashem had known face to face” (Devarim 34:10), “And the man Moshe was exceedingly humble, more than any person on the face of the earth” (Bamidbar 12:3), and the fact that at this juncture Hashem wanted Moshe to stamp his blessing on the Land of Israel, still, in all, if Moshe would not have accepted his death as an atonement for his sin then he would not have joined the other righteous people in Heaven.
We see from here that acceptance of one’s atonement is part of the process of repentance, to cleanse oneself of sin, whatever they may be. One small choice can make the difference between an eternal life full of reward, mixed with punishment, or G-D forbid, worse.