Sub Summarize macro code help needed.....

try this code

Option Explicit

Sub Summarize()
    Dim ws As Worksheet
    Dim lastRng As Range
    
    Application.ScreenUpdating = False 'speed up code
    ThisWorkbook.Sheets("Data Summary").Rows("2:65536").ClearContents 'clear
    For Each ws In Worksheets
        If ws.Name <> "Data Summary" Then
            ws.Range("a3").Select
            Range(ActiveCell, ActiveCell.End(xlDown).End(xlToRight)).Copy
            Sheets("Data Summary").Select
            Range("a3").Select
            If ActiveCell.Offset(1, 0) = "" Then
                ActiveCell.Offset(1, 0).Select
                Else
                ActiveCell.End(xlDown).Offset(1, 0).Select
            End If
            ActiveCell.PasteSpecial (xlPasteValuesAndNumberFormats)
            Application.CutCopyMode = False
        End If
    Next ws
    Application.ScreenUpdating = True
    Sheets("Data Summary").Activate
End Sub

if its not working for you, please post your sample