In September 1969, a mere two months after the successful Apollo 11 moon landing (but in time for the fiscal year 1971 budget) a report landed on Nixon's desk laying out options:

Our horizons and our competence have expanded to the point that we can consider unmanned missions to any region in our solar system; manned bases in earth orbit, lunar orbit or on the surface of the Moon; manned missions to Mars; space transportation systems that carry their payloads into orbit and then return and land as a conventional jet aircraft; reusable nuclear-powered rockets for space operations; remotely controlled roving science vehicles on the Moon or on Mars; and application of space capability to a variety of services of benefit to man here on earth.

This was as optimistic as NASA would ever get about their future plans.

While Nixon was still riding high on the success of Apollo 11 at the time, by the end of the year he was openly wondering why we needed to return to the moon so many times; there was also an intense amount of budget scrutiny. In January 1970, Nixon had a meeting with Thomas Paine (administrator of NASA) and told him that opinion polls and the public's mood both necessitated cuts.

There were, essentially, three main pushes: go to Mars, build a space station, or build a reusable shuttle. The initial budget proposed to Paine made the first infeasible, but the other two were still possible.

However, Congress still had to approve the budget, and Congress's further skepticism led to the station being axed as well. The shuttle might have been gone too, but the military was interested (as a low cost method of launching satellites) and NASA formed a political alliance with the Pentagon in order to maintain sufficient funding.

I should end with noting Nixon didn't have any particular animosity for space, and in his meeting with Paine, he said

You can certainly tell your people that the cuts in NASA were made most reluctantly, and that I am committed to the space program for the long-term future.

It was a practical matter of budget combined with the political winds. In a March 1970 statement from Nixon (which you can read here), he wrote

We must build on the successes of the past, always reaching out for new achievements. But we must also recognize that many critical problems here on this planet make high priority demands on our attention and our resources. By no means should we allow our space program to stagnate. But -- with the entire future and the entire universe before us -- we should not try to do everything at once. Our approach to space must continue to be bold -- but it must also be balanced.

Sources (the Logsdon is the one I'd recommend if you want to learn more):

Heppenheimer, T. A. (1999). The Space Shuttle Decision: NASA's Search for a Reusable Space Vehicle. United States: National Aeronautics and Space Administration, NASA History Office, Office of Policy and Plans.

Launius, Roger D. "NASA and the Decision to Build the Space Shuttle, 1969–72." Historian 57.1 (1994): 17-34.

Logsdon, J. M. (2015). After Apollo? Richard Nixon and the American Space Program. United Kingdom: Palgrave Macmillan.