Coping Data from 800+ Sheets to Master Table using VBA

New to using VBA for anything other than simple recorded Macros.

I've inherited a workbook that is currently using indirect formulas to refence cells across the workbook. The problem with that is there's about 800 worksheets and 125 columns in the master table. In order for to workbook to be functional currently auto calculate formulas is off and there's a macro to enable it to refresh the data. There's an effort to put this into a database but that's going to take longer than then our current needs.

I want to get rid of the indirect formulas to make the workbook more stable.

I need to build a master table that displays the data from each worksheet. The worksheets are form based so it's not simply coping several tables into one. I've tried recording a macro to do the copy and it works partially. It's super massive with the 125 fields and doesn't shift to the next row as we move to the next worksheet.

If someone could help on making the code cleaner and help on moving to the next row I'd appreciate it. Below is a shorter version of the code, only 2 examples not all 125.

Dim ws As Worksheet
Dim i As Integer

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
    End With
    
        
For i = 9 To Worksheets.Count - 1
    Sheets(i).Select
    Range("H7").Select
    Selection.Copy
    Sheets("Master Report").Select
    Range("D6").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets(i).Select
    Range("P7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Master Report").Select
    Range("E6").Select

Next i

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
    End With