I have a workbook with 50 worksheets. If I wanted to gather all the data
from each worksheet and then paste it onto one worksheet named Report, I
could use the following code and repeat until I have covered all 50
worksheets. However I am wondering can this code be shortened as well as
modified so that it only copies cells from the range A1:B24 that have
contents inside them? Also on the reports page where all the results are
pasted into Column A, is it possible to delete the rows that do not have any
contents in them?

Sub CopyPasteCode()
Sheets.Add
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Report"
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