Using VBA to automate copy/paste on new Sheet

We need a bit more information:

Are you wanting one summary sheet per workbook?
Are you wanting to copy all rows below the headings from each sheet?
Is it all columns in each sheet?
Do the ranges vary with time
etc

Perhaps you could attach an example workbook, with each sheet included, plus the resultant summary sheet

thanks

@kev_ thank you for the quick response.

I want one new summary page made from the each individual copy/paste, I don't need any headers and the range to be copied is constant on every sheet within the workbook, but the individual sheets will be different for every workbook.

Attached is the workbook I'm using. You'll notice there is a button named "Gail". That is used to copy/paste info since we have to take info and dump it into another program.

I'd like to be able to have one button on the index page that does that same copy/paste on each individual sheet into a new worksheet named "Gail"

The names of each of the sheets will change based on each job and there might be one sheet or 20. This is where I'm lost

I have had a look at your workbook.

1. Please confirm that
- all hidden sheets should be excluded from the macro
- Sheet named "Index" is to be excluded from the macro
- all other sheets should be included in the macro

2. Where have you hidden button "Gail"?
(and I did not find any code for a button click event anywhere in the VBA)

3. What is the specific range you want copying from each remaining sheet?

4. When pasting into worksheet "Gail" the data will paste in sheet order

5. In worksheet "Gail", the rows from one sheet will follow on immediately from the previous sheet

6. Do you want a sheet identifier against each entries on Sheet "Gail"
- for example ColumnA could include the sheet name

7 Are you wanting the values copy/pasted
OR
do you want sheet Gail to contain formula?
(so that change a in value in a subsidiary sheet is reflected in sheet Gail

8 Is running the macro a once only event per workbook?
OR
is it a regular (eg monthly) task

9 Do the headings in "Gail" mirror those in copied sheets?

@kev_
All hidden sheets would be excluded as well as the index page, but the sheets will almost always have different names than what you're seeing in the template
The Gail button is close to cell S80, it should be tied to ProContractor macro in the sheet, that macro has the range of what I will be copying/pasting
pasting in sheet order is perfect and will follow immediately after each other.
We don't need any Identifiers, as once all this is pasted into "Gail", that content then gets pasted into different program
I only need values pasted into Gails Sheet
The macro would typically be run once, but there are cases where we may have to rerun the macro if we change our estimate
The headings in Gail would mirror those copied. The headings are in cells S81:AC81

Thank you for going over this and helping me!

Put this vba in a module and give it a try.
Notes make it largely self explanatory. Please ask if anything is not clear.

The condition to delete rows where units =0 means that there are rows with values in other columns being deleted. It is consistent with ProContractor macro, but is it what you want?

Sub CopySheetsToGail()
Application.ScreenUpdating = False
    
    Dim ws As Worksheet, Gail As Worksheet
    Dim cel As Range
    Dim msg As String
    Dim nextRow As Long, myCount As Long
    
    msg = "Sheets copied to Gail: " & vbCr & vbCr
    myCount = 0

'create new worksheet "Gail" (old sheet "Gail" is deleted)
    Application.DisplayAlerts = False
        On Error Resume Next
        Sheets("Gail").Delete
        Err.Clear
        With ThisWorkbook
            Set Gail = .Sheets.Add(After:=.Sheets(.Sheets.count))
            Gail.Name = "Gail"
        End With
    Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Worksheets
    If ws.Visible = True And UCase(ws.Range("AR1").Value) = "TASK CODE" Then
        msg = msg & ws.Name & vbCr
        
'place headings in sheet "Gail"
       myCount = myCount + 1
       If myCount = 1 Then ws.Range("AR1:BB1").Copy Destination:=Gail.Range("A1")
        
'copy other values
        nextRow = Gail.UsedRange.Rows.count + 1
        ws.Range("AR3:BB28").Copy
        Gail.Range("A" & nextRow).PasteSpecial xlPasteValues
        Gail.Range("A" & nextRow).PasteSpecial xlPasteColumnWidths
    End If
Next ws
'tidy up
With Gail
'delete blank rows
        .Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'delete rows where units = 0
        lastRow = .UsedRange.Rows.count
        .Range("A1").AutoFilter Field:=3, Criteria1:="0.00"
        .Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilterMode = False
'change text to upper case
        For Each cel In .UsedRange
            cel.Value = UCase(cel.Value)
        Next cel
        .Range("A1").Select
End With

MsgBox msg
Application.ScreenUpdating = True

End Sub

@kev_ I just tried it quickly with a test sheet and it looks great! I will try it in a couple sheets today and see how it works. Thank you so much for your help!