Excel Sheets data copy and paste into one sheet

Yep, it can be done......However, you need to give us something to work with....Upload a sample file depicting a before & after.

Right, here you go...this will get you started:
Open a folder on your desktop called REPORTS.....Put all your reports received into this folder.
Paste this code into your MainSheet.Workbook as per sample attached.
The code opens the folder and one by one copies the information from the report sheets to your mainSheet.

Option Explicit
Sub ReportCopy()
Dim CopyFromPath As String, FileName As String
Dim CopyFrom As Workbook, CopyToWb As Workbook, wb As Workbook
Dim rngNames As Range, rng As Range
Dim lRow As Long, nextrow As Long, r As Long
Application.ScreenUpdating = False
CopyFromPath = "C:\Users\Sintek\Desktop\Reports\" 'change to your Path
Set CopyToWb = Workbooks("MainSheet.xlsm")
FileName = Dir(CopyFromPath & "*.xlsx")

Do While Len(FileName) > 0
    Set wb = Workbooks.Open(CopyFromPath & FileName)
        With wb.Sheets("Sheet1")
            lRow = .Cells(Rows.Count, "A").End(xlUp).Row
            For r = 2 To lRow
                .Range("A" & r & ":Z" & r).Copy
                nextrow = CopyToWb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
                CopyToWb.Sheets("Sheet1").Range("A" & nextrow).PasteSpecial xlPasteAll
                           
            Next r
                Application.CutCopyMode = False
                wb.Close saveChanges:=False
        End With
        FileName = Dir
Loop
Application.ScreenUpdating = True
'ActiveWorkbook.Close saveChanges:=True
End Sub

PS....I noticed that you would like to increment the inputs in Column A
Substitute with this code:

Option Explicit

Private Sub CommandButton1_Click()
Dim CopyFromPath As String, FileName As String, sNo As Long
Dim CopyFrom As Workbook, CopyToWb As Workbook, wb As Workbook
Dim rngNames As Range, rng As Range
Dim lRow As Long, nextrow As Long, r As Long, lRow2 As Long
Application.ScreenUpdating = False
CopyFromPath = "C:\Users\Sintek\Desktop\Reports\"
Set CopyToWb = Workbooks("Copy of Mainsheet.xlsm")
FileName = Dir(CopyFromPath & "*.xlsx")

Do While Len(FileName) > 0
    Set wb = Workbooks.Open(CopyFromPath & FileName)
    
    With wb.Sheets("Sheet1")
        lRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For r = 2 To lRow
            .Range("B" & r & ":Z" & r).Copy
            nextrow = CopyToWb.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row + 1
             CopyToWb.Sheets("Sheet1").Range("B" & nextrow).PasteSpecial xlPasteAll
            CopyToWb.Sheets("Sheet1").Range("A" & nextrow) = nextrow - 1
        Next r
            Application.CutCopyMode = False
            wb.Close saveChanges:=False
    End With
    
    FileName = Dir
Loop
Application.ScreenUpdating = True
'ActiveWorkbook.Close saveChanges:=True
End Sub

See amended file upload and code in post 6

Did you do the following:
Open a folder on your desktop called REPORTS.....Put all your reports received into this folder.
Change this line of code to your Path

CopyFromPath = "C:\Users\Sintek\Desktop\Reports\"

Change this line of code to your worksheet name

Set CopyToWb = Workbooks("Copy of Mainsheet.xlsm")

You did not change this line

Set CopyToWb = Workbooks("Copy of Mainsheet.xlsm")

to

Set CopyToWb = Workbooks("Mainsheet.xlsm")

Use the following Sample files I uploaded to see if it works..Keep the names the same. Other than that there is nothing more I can do....
Put the 2 sample files in your Report Folder
Put MainSheet on your desktop....Remember to change your path

Works perfectly from my side....
Explian to me what you have done so we can work through this:

Like I said...It works perfectly from my side........Only thing i can think of is that your path is incorrect

Did you change your path

use the following sample files i uploaded to see if it works..keep the names the same. Other than that there is nothing more i can do....
Put the 2 sample files in your report folder
put mainsheet on your desktop....remember to change your path

Are you sure this is correct:

"C:\Users\travelmall\Desktop\REPORTS"

Open the folder called [Reports]
Right click on any of the report files and click properties....See the location

Then I am at a loss....
This is the result i get after running the code from MainSheet

Just found your problem !!!!!!

"C:\Users\travelmall\Desktop\REPORTS\"

2007, yes am able to click the button.....However, when my path is incorrect....Nothing happens

Yes must be there

After the code runs....... delete the files in Reports Folder......This way there will never be duplicates.

Put this code after

Loop

before

Application.ScreenUpdating = True

On Error Resume Next
Kill "C:\Users\travelmall\Desktop\REPORTS\*.xls"
On Error GoTo 0

First of all....After everything does the code work perfectly?
If so please mark thread as solved and add to reputation....

is it possible to protect the main sheet or how to make read-only format????

Ideally it is ime to open a new thread with a new request.