The phrase has a historical link to drinking from cups; it's metaphorical for being drunk. After several toasts, Sir Walter was decidedly in his cups.
Pro Tip 2/3
Old-fashioned Usage
It's an older phrase, so it can lend a vintage or formal tone to the speech. At the family reunion, Uncle George was a bit in his cups.
Pro Tip 3/3
Caution in Usage
While not offensive, use carefully as it refers indirectly to someone's drinking. You might want to avoid discussing him being in his cups at the meeting.