I changed the workbook event to manual running of code... Please click the button which is there in the summary sheet to get data from all sheets, except the first 3 sheets (Including the hidden sheet).
Sub GetDataFromAllSheets()
Dim Sh As Worksheet, sConc As String, iWsNo As Integer, sBase As String
sBase = "SUMMARY"
For Each Sh In ThisWorkbook.Sheets
iWsNo = Sh.Index
If iWsNo > 3 Then
With Sh
sConc = .Range("C8") & " " & .Range("C10") & " " & .Range("C12") & _
" " & .Range("C14") & " " & .Range("C16") & " " & .Range("C18") & _
" " & .Range("C20") & " " & .Range("C23") & " " & .Range("C25") & _
" " & .Range("C27") & " " & .Range("C34") & " " & .Range("C36") & _
" " & .Range("C38")
End With
With Sheets(sBase)
.Cells(iWsNo, "A").Value = Sh.Range("B1").Value 'Last Name
.Cells(iWsNo, "B").Value = Sh.Range("G1").Value 'First Name
.Cells(iWsNo, "C").Value = Sh.Range("B3").Value 'Division
.Cells(iWsNo, "D").Value = sConc 'Desc
.Cells(iWsNo, "E").Value = Sh.Range("C28").Value 'Total Hours
End With
End If
Next Sh
End Sub
Refer the attached file for details
Bookmarks