I have a workbook with 50 worksheets. The first 2 are named Question and
Status. If I wanted to gather all the data from each worksheet and then
paste it onto one worksheet named Summary Report, I could use the following
code I pasted below and repeat until I have covered all 50 worksheets.

Sub CopyPasteCode()
Sheets("Quest 1").Select
Range("A1:B24").Select
Selection.Copy
Sheets("Report").Select
ActiveSheet.Paste
Sheets("Quest 2").Select
Range("A1:B24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Report").Select
Range("A25").Select
ActiveSheet.Paste
Sheets("Quest 3").Select
Range("A1:B24").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Report").Select
Range("A49").Select
ActiveSheet.Paste
End Sub

However I know this code be shortened as well as modified so that it only
copies cells from the range A1:B24 that have contents inside them but I don't
know how.
I also tried the following code I posted below, but its not working. Not
sure if it's me making a mistake or the code is not appropriate. Any
thoughts?

Sub CopyRangeFromMultiWorksheets()
Set CopyRng = sh.UsedRange
Dim sh As Quest
Dim DestSh As Quest
Dim Last As Long
Dim CopyRng As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Delete the sheet "RDBMergeSheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ActiveWorkbook.Worksheets("RDBMergeSheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "RDBMergeSheet"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "RDBMergeSheet"

'loop through all worksheets and copy the data to the DestSh
For Each sh In ActiveWorkbook.Worksheets
If sh.Name < DestSh.Name Then

'Find the last row with data on the DestSh
Last = LastRow(DestSh)

'Fill in the range that you want to copy
Set CopyRng = sh.Range("A1:G1")

'Test if there enough rows in the DestSh to copy all the data
If Last + CopyRng.Rows.Count DestSh.Rows.Count Then
MsgBox "There are not enough rows in the Destsh"
GoTo ExitTheSub
End If

'This example copies values/formats, if you only want to copy the
'values or want to copy everything look at the example below
this macro

With CopyRng
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

CopyRng.Copy
With DestSh.Cells(Last + 1, "A")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With

'Optional: This will copy the sheet name in the H column
DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

End If
Next

ExitTheSub:

Application.Goto DestSh.Cells(1)

'AutoFit the column width in the DestSh sheet
DestSh.Columns.AutoFit

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub