[SOLVED] Combining multiple files into one but subscript out of range error on second iteration of loop

Combining multiple files into one but subscript out of range error on second iteration of loop

I have a folder of .csv files, each containing one column of data, but the number of rows varies.
I want to copy the data from each .csv file onto an output worksheet (inside a master workbook where the VBA code lives).
The .csv files are numbered sequentially (say, 1.csv, 2.csv, etc.).
On the output worksheet, I want to place the copied data side by side (so the data from 1.csv will be pasted in column A in output and data from 2.csv will be pasted in column B in output and so on.

I wrote a loop to copy the data from each .csv file, to a worksheet called scratch in the same master workbook.
Then I copy the data to output, to do something to the data.

It ran the first time, but as soon as it progressed to 2.csv, I got an error where I copy the .csv data to scratch.

subscript out of range

I'm trying to understand why I get the error. (In terms of approach I know how to do it because the code worked several years ago.)

Public Sub Combine()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim filePath As String  'folder path of csv files, numbered sequentially, say if 10 files 1, 2, 3.......10.csv)
Dim fileMin As Integer  'number of the first csv file (e.g. 1)
Dim fileMax As Integer   'number of the last csv file (e.g. 10)
Dim fileNumber As Integer
Dim fileName As String

Dim rngSource As Range
Dim CLastFundRow As Long
Dim CFirstBlankCol As Long

'I'm on a mac
filePath = ThisWorkbook.Path
If Right(filePath, 1) = ":" Then
    filePath = filePath & "Folder/"
    Else
    filePath = filePath & "/Folder/"
End If
fileMin = 1
fileMax = 10

For fileNumber = fileMin To fileMax
    fileName = fileNumber & ".csv"
    Workbooks.OpenText (filePath & fileName)
    Windows(fileName).Activate

    'Copy and paste data from .csv file to Scratch
    'where on 2nd loop "subscript out of range" error occur
    Workbooks("Master.xlsm").Worksheets("Scratch").Range("A1:A100").Value2 = Workbooks(fileName).Worksheets(fileNumber).Range("A1:A100").Value2
    
    Windows(fileName).Close saveChanges:=False
    Workbooks("Master.xlsm").Activate
    
    'Finds last row of content in Scratch
    CLastFundRow = Workbooks("Master.xlsm").Worksheets("Scratch").Cells(Rows.Count, 1).End(xlUp).Row

    'Finds first column without content in Output
    CFirstBlankCol = Workbooks("Master.xlsm").Worksheets("Output").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    
    'Copy and paste data from Scratch to Output
    Set rngSource = Workbooks("Master.xlsm").Worksheets("Scratch").Range("A1:A" & CLastFundRow)

    With ThisWorkbook.Worksheets("Output")
        .Range(.Cells(1, CFirstBlankCol), .Cells(CLastFundRow, CFirstBlankCol + 1)).Value2 = rngSource.Value2
    End With
  
    Set rngSource = Nothing
    Workbooks("Output.xlsm").Worksheets("Scratch").Cells.Clear
    
Next

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Solution

As @CDP1802 pointed out, my issue has to do with fileNumber, .csv files only have one sheet. after correcting that, my original code works. although new issue cropped up, namely I get prompted to grant access whenever the code wants to access each file (that the code is trying to copy then paste into the master). I found out this has to do with Apple's sandbox rules. Moral of the story is, MS products don't play nicely with Mac. Go figures. I worked around this issue by using Applescript (to open all my files then close it in Excel). Or you can do what the commenter did in this post (scroll down) and rename your files to make it appear as "temporary" to skirt around the sandbox rules.