VBA Code to exclude sheets with no data

[angelmaine4ever]

Hi I have this formula below (thanks Rick Rothstein!) that combines all worksheet data in to one worksheet.

However, I need to exclude those worksheets/tab with no data at all (only with header).

PLEASE HELP ME! THANK YOU!!!

Sub Button2_Click()
Dim NextRow As Long, Sht As Worksheet
For Each Sht In Worksheets
If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Offset(1).Value
End If
Next
End Sub

 

[FormR]

I need to exclude those worksheets/tab with no data at all (only with header).

Hi, you should probably have posted this in your original thread, but maybe this amendment will work for you.

Sub Button2_Click()
Dim NextRow As Long, Sht As Worksheet
For Each Sht In Worksheets
  If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
   NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
     If Sht.UsedRange.Rows.Count > 1 Then
       Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
       Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Offset(1).Value
     End If
  End If
Next
End Sub

 

[angelmaine4ever]

Hi, you should probably have posted this in your original thread, but maybe this amendment will work for you.

Sub Button2_Click()
Dim NextRow As Long, Sht As Worksheet
For Each Sht In Worksheets
  If Sht.Name <> "Master" And Sht.Name <> "READ ME" And Sht.Name <> "PRIDE Ratings' Distribution" And Sht.Name <> "Computation" Then
   NextRow = Sheets("Master").Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1
     If Sht.UsedRange.Rows.Count > 1 Then
       Sheets("Master").Cells(NextRow, "A").Value = Sht.Name
       Sheets("Master").Cells(NextRow, "B").Resize(Sht.UsedRange.Rows.Count, 9) = Sht.UsedRange.Offset(1).Value
     End If
  End If
Next
End Sub

Sir, thank you!!! It worked!!!