Open files & copy data to sheet

[honkin]

I need to be able to open a number of files from a folder and have the contents, bar the header row, copied to a file.

I already have a workable macro which does the copying of the contents, but to do this, I have to open each individual file and run the macro. The contents are then copied to the next available cell in column B and I have to manually add the filename in column A

Here is that code

Sub FAL_FA_Weekly()
'
' Predictology
' This macro copies and pastes to the Predictology file
'
    With ActiveSheet
        With .Cells(1).CurrentRegion
            .HorizontalAlignment = xlCenter
            .Offset(1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Predictology-Reports Football Advisor.xlsx").Sheets("FAL").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
    End With
    Application.CutCopyMode = False
End Sub

The downside of this is, not just having to do each file manually, but also having to add the filename (sans extension) to column A next to each entry.

Below is my first attempt at creating a macro for opening files and copying the content

Sub Create_Month_Summary()

    Dim folderPath As String
    Dim fileName As String
    Dim thisWorkbook As Workbook
    
    'Folder containing FAL files
    
    folderPath = "/Volumes/DOCUMENTS/Horse/Football Advisor/New Role/Predictology/FAL & FLP/"
    
    Set thisWorkbook = ActiveWorkbook
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath & "\"
    
    fileName = Dir(folderPath & "*.csv")
    Do While fileName <> ""
        
        With ActiveSheet
        With .Cells(1).CurrentRegion
            .HorizontalAlignment = xlCenter
            .Offset(1).SpecialCells(xlCellTypeVisible).Copy
        Workbooks("Predictology-Reports Football Advisor.xlsx").Sheets("FAL").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
        End With
    End With
    'Get next file name

fileName = Dir
Loop
    Application.CutCopyMode = False
    
End Sub

I opened the destination file and ran the macro, but I have obviously done something wrong as absolutely nothing happens.

The files all have names like this FAL1.csv, FAL2.csv, FAL3.csv and so on, so the ultimate aim is to have the contents copied and pasted starting at column B, then the filename (FAL1) added to column A

Any help gladly accepted on this one.

cheers

 

[Micron]

Hard to troubleshoot code when you don't have the referenced files, paths, etc. Suggest you step through (F8) and check your variables as you go. You can inquire in immediate window:
?fileName
and hit return to see what it contains. Note that a line has to execute before it can set or edit anything.
I doubt 'nothing' happens - unless your code is never called at all.
One thing that looks odd is using forward slashes and looking for a backslash.
Unfortunately I cannot help with the copy/paste part as I know some vba but not much about Excel operations. I'm fairly certain I've seen lots of threads everywhere for opening and copying/pasting between files but have never needed to use it.