Macro to Combine data from different Sheets and Sheet names to a "Summary Worksheet"

Hello,

I'm trying to create a macro for one of my reports in the office.

The macro should copy each sheet names and paste it to Column A of the Summary Sheet, while the data on each sheet should be copied and pasted to column B of the Summary sheet, but should not include any data from the tab "Rejected Ballots".

After lots of research (since I'm literally a newbie in excel VBA) I was able to combine the below command:

Sub Audit()

Dim A As Integer
Dim S As Worksheet

'Delete the summary sheet if it exists.
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Summary"

If Range("B1").Value = ("") Then
Columns(1).Insert
For i = 1 To Sheets.Count
Cells(i, 1) = Sheets(i).Name
Next i
End If

Sheets(2).Activate
Range("A1").EntireColumn.Select
Selection.Copy Destination:=Sheets(1).Range("B1")

End Sub

But it looks like, this is wrong (sadly).

Can someone please help me?

Please note that the report sometimes have 30 sheets and the data for each sheet will always be in column A and sometimes it will reach the cell "A300". I have attached a sample of my report.

Thanks in advance,

Jeremie