According to the official Counter-Strike Workshop FAQ, subscribing to a Workshop map automatically downloads it and adds it to the Workshop tab in the in-game map browser.
Play a Workshop map with friends
- Subscribe to the desired Workshop map.
- Start a Play With Friends lobby.
- Invite your friends.
- Select the subscribed map.
- Set the lobby permissions to Private Match.
- Start the local server.
Steam also identifies maps in the Counter-Strike 2 Workshop as ready-to-use items and says a subscribed map will be available when Counter-Strike 2 starts.