excel VBA, create a column with sheet or file name in cells while copying multiple CSV files to one workbook

Question

I’ve got 700 CSV files with 7 columns 1000 rows each and I need to get them in one long column. Sample code is doing the copying but I don’t know how to get it to create a column (the same length as the other columns in that file) with sheet or file name in each cell before copying. I really only need column A (dates), created column (sheet name) and column F (values) from each CSV file, in that order if thats possible.

    Sub ImportData()
Dim lastrow As Long
Dim clastrow As Long
Dim filePath As String
Dim fileName As String
Dim count As Long
Dim importRange As Range
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Dim cws As Excel.Worksheet
count = 0
Set cws = ThisWorkbook.Sheets(2)
filePath = "C:\Users\user\Desktop\CSV files\"
fileName = Dir(filePath & "*.csv")
Do While fileName <> ""
    count = count + 1
    Set wb = Excel.Workbooks.Open(filePath & fileName)
    Set ws = wb.Worksheets(1)
    lastrow = ws.Cells(Rows.count, "a").End(xlUp).Row
    clastrow = cws.Cells(Rows.count, "a").End(xlUp).Row + 1
    Set importRange = ws.Range("a2:f" & lastrow)           'skips header row
'    cws.Cells(clastrow, 1).End(xlUp).Offset(1, 0).Resize(importRange.Rows.count, importRange.Columns.count) = importRange.Value
    importRange.Copy
    cws.Cells(clastrow, "a").PasteSpecial xlPasteValues
    wb.Application.CutCopyMode = False
    wb.Close
    fileName = Dir
Loop
End Sub

Answer 1

Copy Values by Assignment

• Not tested.

The Code

Option Explicit
    
Sub importData()
    
    ' Define constants.
    Const FilePath As String = "C:\Users\user\Desktop\CSV files\"
    
    ' Define Destination First Cell.
    Dim drg As Range
    With ThisWorkbook.Sheets(2)
        Set drg = .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
    End With
    
    ' Write the first file name to a variable.
    Dim FileName As String: FileName = Dir(FilePath & "*.csv")
    
    ' Declare additional variables.
    Dim srg As Range ' Source Range
    Dim sLastRow As Long ' Source Last Row
    Dim srCount As Long ' Source Rows Count
    Dim fCount As Long ' Files Count
    
    ' Copy values by assignment.
    Application.ScreenUpdating = False
    Do While FileName <> ""
        With Workbooks.Open(FilePath & FileName).Worksheets(1)
            sLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            If sLastRow >= 2 Then
                fCount = fCount + 1
                Set srg = .Range("A2:F" & sLastRow)
                srCount = srg.Rows.Count
                Set drg = drg.Resize(srCount)
                drg.Value = srg.Columns(1).Value
                drg.Offset(, 1).Value = .Name
                drg.Offset(, 2).Value = srg.Columns(6).Value
                Set drg = drg.Cells(1).Offset(srCount)
            End If
            .Parent.Close SaveChanges:=False
        End With
        FileName = Dir
    Loop
    'drg.Worksheet.Parent.Save
    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "Files processed: " & fCount, vbInformation, "Success"

End Sub