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.