Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CopyPasteCode
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 |