Catholics and Orthodox do not subscribe to the doctrine of Sola Scriptura, which is the doctrine that says that the only source of authoritative teaching is Sacred Scripture. Instead, we rely on Apostolic Succession to show that the doctrines we hold onto are those that the Apostles taught, by tracing the teachers and the teaching in an unbroken chain back to the Apostolic era. Regarding the specific issue of praying for the Dead, we have both the Sacramentary of Serapion and a Homily from St. John Chrysostom attesting to the antiquity of the practice already in the Fourth Century.
However, since the question asks for specifically Scriptural support for prayers for the Dead, the most important passage is the end of 2nd Maccabees 12:
39 And upon the day following, as the use had been, Judas and his company came to take up the bodies of them that were slain, and to bury them with their kinsmen in their fathers' graves.
40 Now under the coats of every one that was slain they found things consecrated to the idols of the Jamnites, which is forbidden the Jews by the law. Then every man saw that this was the cause wherefore they were slain.
41 All men therefore praising the Lord, the righteous Judge, who had opened the things that were hid,
42 Betook themselves unto prayer, and besought him that the sin committed might wholly be put out of remembrance. Besides, that noble Judas exhorted the people to keep themselves from sin, forsomuch as they saw before their eyes the things that came to pass for the sins of those that were slain.
43 And when he had made a gathering throughout the company to the sum of two thousand drachms of silver, he sent it to Jerusalem to offer a sin offering, doing therein very well and honestly, in that he was mindful of the resurrection:
44 For if he had not hoped that they that were slain should have risen again, it had been superfluous and vain to pray for the dead.
45 And also in that he perceived that there was great favour laid up for those that died godly, it was an holy and good thought. Whereupon he made a reconciliation for the dead, that they might be delivered from sin.
Here, part of Judas Maccabeus's army died fighting the Gorgias, military governor of Idumea. It was found that those who died had kept Jamnite idols, which is a sin. However, they had fought for Israel and to defend their homeland, so the people prayed that the Lord might forgive them this offense so that they might be raised when the Resurrection came. As verse 44 says, "for if he had not hoped that they that were slain should have risen again, it had been superfluous and vain to pray for the dead." But because he hoped for the Resurrection, it is not vain to pray for them.
Of course, your argument is that prayer for the dead is vain because they are all asleep. In fact, the doctrine of Mortalism (sometimes known as "soul sleep") is postulated exactly to justify the vanity of praying for the dead, or of intercessory prayer by the dead. But there are passages in Scripture where we see the dead specifically not sleeping. Particularly, in the passage of the Transfiguration (Mt 17:1–13; Mk 9:2–13; Lk 9:28–36) Moses and Elijah are standing together with Jesus and talking.
Mt 17:3 And, behold, there appeared unto them Moses and Elias talking with him.
Mk 9:4 And there appeared unto them Elias with Moses: and they were talking with Jesus.
Lk 9:30–31 And, behold, there talked with him two men, which were Moses and Elias: 31 Who appeared in glory, and spake of his decease which he should accomplish at Jerusalem.
Also, in the parable of the Rich Man and Lazarus (Lk 16:19–31), the deceased are shown awake and concerned about things on Earth:
23 And in hell he lift up his eyes, being in torments, and seeth Abraham afar off, and Lazarus in his bosom. 24 And he cried and said, Father Abraham, have mercy on me, and send Lazarus, that he may dip the tip of his finger in water, and cool my tongue; for I am tormented in this flame.
In Revelation, too, nowhere are the dead in Christ described as sleeping. Particularly, the twenty-four Elders in Revelation 4:4.10–11:
4 And round about the throne [were] four and twenty seats: and upon the seats I saw four and twenty elders sitting, clothed in white raiment; and they had on their heads crowns of gold.
10 The four and twenty elders fall down before him that sat on the throne, and worship him that liveth for ever and ever, and cast their crowns before the throne, saying,
11 Thou art worthy, O Lord, to receive glory and honour and power: for thou hast created all things, and for thy pleasure they are and were created.
In the Psalms, a few references to the state of souls after death, for example:
Ps 16(17):9–11 Therefore my heart is glad, and my glory rejoiceth: my flesh also shall rest in hope. 10 For thou wilt not leave my soul in hell; neither wilt thou suffer thine Holy One to see corruption. 11 Thou wilt shew me the path of life: in thy presence [is] fulness of joy; at thy right hand [there are] pleasures for evermore.
In conclusion, there are many different positions on the nature of the soul and their destination after death in the Old Testament; and in the New Testament, the wide use of the verb κοιμάω "to sleep" to stand for θνῄσκω "to die" causes a misunderstanding (which unfortunately Luther was party to, which caused it to become a widely-held opinion among Protestants) that death is a literal sleep. This was not taught by the primitive Christian communities, as any review of the praxis of these communities will attest.