Inefficient 'select sheets' macro - needs improving pls!

Hi Rob,

please try the following - since you didn't include a sample workbook I couldn't test it, but it should work

Sub get_number()

On Error Goto Err1
Application.ScreenUpdating = False
Application.EnableEvents = False

Dim ws as WorkSheet
Dim count&
count = 3   'row 3 on sheet PCC_SUMMARY

For Each ws In ActiveWorkbook.Sheets
  
Sheets("PCC_SUMMARY").Cells(count, 16) = ws.Range("BC88")
count = count + 1

Next ws

Err1:
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Please click the * below if this helps

This should make it a bit faster, and also prevents the macro from reading cell BC88 on the PCC_SUMMARY sheet.
If you still want it to read from that sheet then remove the if and if end lines

Sub get_number()
Application.ScreenUpdating = False
Dim ws As Worksheet
Count = 3   'row 3 on sheet PCC_SUMMARY

For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "PCC_SUMMARY" Then
    
        Sheets("PCC_SUMMARY").Cells(Count, 16) = ws.Range("BC88")
        Count = Count + 1
    End If
Next ws
Application.ScreenUpdating = True
End Sub

Edit - Ah, beaten to it

Rob.

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.