Workbook Summary Sheet

[TGalin]

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

 

[ryguy7272]

This should do what you want:
Sub CopyRangeFromMultiWorksheets()
Dim sh As Worksheet
Dim DestSh As Worksheet
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 = "Summary Report"

'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:B24")

'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
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


Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function


Function LastCol(sh As Worksheet)
On Error Resume Next
LastCol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function



Regards,
Ryan---

 

[TGalin]

Wow, this is nice. Thank you. I am having a small problem when I run the
code, not sure if it's something I did incorrectly.

When, I run the macro it jumps to VB and a Microsoft Visual Basic Help box
appears with the message "Compile error: Sub or Function not defined and the
part of the code I posted below that says "this" is highlighted in blue.

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

 

[Dave Peterson]

"this macro" is just more of the comment that appeared on the preceding line.

You can either move it up to that line, delete the line, or put an apostrophe in
front of it.

Be aware that there is another similar problem:

DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

should be on one line:

DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name

 

[TGalin]

Thank you. You were right. It works now.

"this macro" is just more of the comment that appeared on the preceding line.

You can either move it up to that line, delete the line, or put an apostrophe in
front of it.

Be aware that there is another similar problem:

DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value =
sh.Name

should be on one line:

DestSh.Cells(Last + 1, "H").Resize(CopyRng.Rows.Count).Value = sh.Name