How to combine 100 workbooks into a master workbook using VBA?

Question

This is the attempt to do so:

Sub GetSheets()

Dim Path As Variant
Dim filename As Variant
Dim sheet As Variant

Path = "C:\Users\Asus\Google Drive\Income Generating\Stock Market\Daily Quotes\PSEGet"
filename = Dir(Path & "\*.csv")
  Do While filename <> ""
  Workbooks.Open filename:=Path & filename, ReadOnly:=True
     For Each sheet In ActiveWorkbook.Sheets
     sheet.Copy After:=ThisWorkbook.Sheets(1)
  Next sheet
     Workbooks(filename).Close
     filename = Dir()
  Loop
End Sub

When I press the Run Macro button, the file path is recognized, but the copying of the rows of data is not working. This error appears:

These are the files that I want to combine into a master worksheet. Located in one folder:

The files I want to combine are shown here: Sample of CSV file

Answer 1

I came out with a working solution:

Option Explicit
Sub GetSheets()

Dim Path As Variant
Dim filename As Variant
Dim sheet As Variant
Dim Total_rows_active As Long
Dim Total_rows_compiled As Long

Application.DisplayAlerts = False

Path = "C:\Users\Asus\Google Drive\Income Generating\Stock Market\Daily Quotes\PSEGet\"
filename = Dir(Path & "\*.csv")
    Do While filename <> ""
    Total_rows_compiled = Workbooks("Compiled Stock Quotes.xlsx").Worksheets("Compiled").Range("A" & Rows.Count).End(xlUp).Row
    Workbooks.Open filename:=Path & filename, ReadOnly:=True
    Total_rows_active = Workbooks(filename).Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Workbooks(filename).Sheets(1).Range("A1:G" & Total_rows_active).Copy
    Workbooks("Compiled Stock Quotes.xlsx").Worksheets("Compiled").Range("A" & Total_rows_compiled + 1, "G" & Total_rows_compiled + Total_rows_active).PasteSpecial xlPasteAll
    Workbooks(filename).Close
    filename = Dir()
  Loop

The output of the code is here: Compiled File