Help with creating a macro to anaylse and pull data from multiple worksheets

Try this code

Option Explicit

Sub update_summary()
Dim i As Long, lastrow As Long

For i = 1 To Worksheets.Count
    With Worksheets(i)
        If .Name <> "Summary" Then
            If .Range("D35").Value < 1 Then
                lastrow = Worksheets("Summary").Range("A" & Rows.Count).End(xlUp).Row
                Worksheets("Summary").Range("A" & lastrow + 1).Value = .Range("C3").Value
                Worksheets("Summary").Range("B" & lastrow + 1).Value = .Range("C5").Value
                Worksheets("Summary").Range("C" & lastrow + 1).Value = .Range("D35").Value
                Worksheets("Summary").Range("C" & lastrow + 1).NumberFormat = "0.00%"
            End If
        End If
    End With
Next i
End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose Tools | Macro | Macros
Select a macro in the list, and click the Run button

If you have fresh sheets inserted or some values changed and you want to re-run the summary macro, do you want the summary sheet to be cleared first and then data input?