I am trying to pull the data from 500 CSV files all in one folder (the xlsm file is in the same folder). I have the vba checking to see if there are enough available rows in the sheet to fit the next file to be copied/pasted over, and if there aren't then it will create a new sheet and start from range(a1) on the new sheet. The msgbox at the end is just a double check to make sure all the files are copying over.
The issue is that some of the data is not copying over and I can't figure out why. Thoughts?
Public CurrRow, RemRows, TotRows As Long
Sub Open_CSV()
Dim MyFile, FolderPath, myExtension As String
'Dim MyObj As Object, MySource As Object, file As Variant
Dim csv As Variant
Dim wb As Workbook
Dim strDir As String
Dim fso As Object
Dim objFiles As Object
Dim obj As Object
Dim lngFileCount, lastrow As Long
FolderPath = Application.ActiveWorkbook.Path & Application.PathSeparator 'assigning path to get to both workbooks folder
myExtension = "*.csv" 'only look at csv files
MyFile = Dir(CurDir() & "\" & myExtension) 'setting loop variable to ref folder and files
'getting a count of total number of files in folder
strDir = Application.ActiveWorkbook.Path
Set fso = CreateObject("Scripting.FileSystemObject")
Set objFiles = fso.GetFolder(strDir).Files
lngFileCount = objFiles.Count
y = lngFileCount - 2 'folder file count minus tool.CSV_Import2 file
x = 1 'setting loop counter
Z = 1 'setting counter for new sheets
TotRows = 1048576 'total number of rows per sheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Do While MyFile <> ""
Set wb = Workbooks.Open(Filename:=FolderPath & MyFile)
wb.Activate
With ActiveSheet
lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
If x = 1 Then 'need to make the first paste of data in the first row
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("tool.CSV_Import2.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Else
Range("A1").Select 'making all pastes after the first file to the next empty row
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("tool.CSV_Import2.xlsm").Activate
Range("A1").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
CurrRow = ActiveCell.Row 'last row with data number
RemRows = TotRows - CurrRow 'how many rows are left?
If lastrow < RemRows Then 'if the import has fewer row than available rows
ActiveSheet.Paste
Else
Sheets.Add.Name = Z
Sheets(Z).Activate
Z = Z + 1
Range("A1").Select 'direct paste here is applicable since it's the first paste
ActiveSheet.Paste
End If
End If
wb.Close 'close file
If x = y Then 'if loop counter is equal to number of csv files in folder exit loop
Exit Do
End If
x = x + 1
Loop
MsgBox x
End Sub