That is not how that works in practice, regardless of what the intention is
If I can buy something from outside the US today for $1, and the American-Made version is $2. And, If I have a need for 100 of those items a year, then my annual cost is $100.
If a Tariff is applied to artificially inflate the price for foreign made products to match the domestic price, I may start buying US made, but I'm also going to double my cost from $100 to $200. Sure some portion of that money stays in the economy domestically, and there is value to that, but *I* am the one paying an extra $100 a year. Not the foreign country. They are simply selling their product somewhere else in the world, or for less profit to stay competitive in the US market.
Now, if a Tariff is applied that brings the price ABOVE the cost of a foreign supplier. Say to $2.50, the foreign supplier may or may not bow out, but the domestic supplier now has more head room. They can increase their price to something approaching that "foreign cost + Tariff" and pocket the extra cost. Once again, the foreign supplier is not paying that money. *I am*. And now I'm out $250/year for what used to only cost me $100.
There are justifiable debates to be had about the benefits of that to the economy, or to preventing off-shoring of jobs, or of off-shoring environmental impacts (foreign products are often cheaper due to lax regulations on labor, emissions, quality, etc.). But what is not debatable is who pays for that benefit. The consumer in the country buying those goods are the ones paying that cost. Which is why Tarriffs on necessities (food being a primary example) are bad, becuase they hurt the poorest citizens in the country with the Tariff. Now, tariffs on Luxury Goods? (high end cars, jewelry, exotic fabrics etc.) That is a tax on the rich, and I'm all for that.