Faster the gathering of one row_with_two_ranges per sheet ?

[hemboil]

Hello,

I am looking for ways to faster the gathering process of my data : actually I need to copy only 2 ranges [A(i):P(i) and AA(i):AP(i)] for the same row(i) contained in (quite) all the sheets of my worksheet.

Here is my code so far :

Sub gathering_one_row_with_two_ranges_per_sheet()
<code>Application.ScreenUpdating = False</code>  
RowNumb = 2
  For s = 2 To Sheets.Count
     For lig = 2 To Sheets(s).[A65000].End(xlUp).Row
        If Sheets(s).Cells(lig, 1) = Sheets("regroup").Range("A1") Then
       
             Sheets(s).Rows(lig).Copy Sheets("regroup").Cells(RowNumb, 1)
            'adding name of sheet in Column AAi
             Cells(RowNumb, 27) = Sheets(s).Name
            'adding row number where Ai was found
             Cells(RowNumb, 28) = lig
         
            'adding total # of rows of the sheet
            Sheets("regroup").Cells(RowNumb, 37).End(xlUp)(2) = Sheets(s).UsedRange.Rows.Count
        
            RowNumb = RowNumb + 1
       
        End If
     Next lig
    Next
<code>Application.ScreenUpdating = True</code>
End Sub

Notes :
- I am copying currently the full row(i) as it was faster than just copying only the 2 ranges each time
- There are headers in Sheets("regroup").Range("B1:AP1")
- A(i) can be found at rank #256 for sheet1 and be at rank#458 for sheet2,...and sometimes not available in a sheet.
- I have about about 100 and growing # of sheets and about 1000-6000 rows per sheets
- Every sheets is ranked by column P.

Many thanks in advance