What do I need to do to make people want to be my friend?

I have one really close friend who I only see onece a week for a night out because we live over 30 miles apart I do try to make friends but for some reason im never a permanent part of someones circle of freinds. I have joined clubs so I could meet new people and we have gone out on team nights out to which despite my shyness I will leave it at home and be the life of the party and they always say that im so nice so fun and im a funny person but once the night ends thats it, I meet people out all the time from my ome town and they always go out of their way to get my number and say oh we should go out the next night or meet up but I never hear back from them even when i text them. on another occasion my cousin was cheating on his girlfriend and she was upset and wasn't willing to accept it but her friends did not wan't to hear about it so for about a month I was her shoulder to cry on the person who came around with her in her car at all hours of the night when she wanted to find him and as soon as they mended their relationship and she was happy again I was dropped which isn't the first time something like this has happened and at this point im beggining to think that im only a friend during the bad times when all their regular friends can't be bothered or are unavailible but id like to be there for the good times too and I am at a loss as to what to do