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
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.