tengo problema con esta macro

[exorton]

ya lo hice a mi manera.

Código vb:

Application.ScreenUpdating = False
hojas = Application.Sheets.Count - 5
 
Sheets("Hoja4").Range("B2").Select
 
For i = 1 To hojas
    Sheets(i).Select
    Range("D9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.Cells(1, 7)).Select
    Selection.Copy
    
    Rw = Selection.Rows.Count
    
    Sheets("Hoja4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    
    For a = 1 To Rw
        Selection.End(xlDown).Offset(a - Rw, -1).Value = Sheets(i).Name
    Next a
    
    Selection.End(xlDown).Offset(1, 0).Select
Next i
 
Application.ScreenUpdating = True
 

}

Tiene un tiempo de ejecución mucho mas rápido (son 216 hojas), en como 5 segundos entrega el resultado y creo que esta todo bien.

Gracias por el apoyo y @mrocf favor explícame tu código entiendo parte pero no todo y me gustaría aprender.