Combine 2 sets of VBA codes to open and copy

[honkin]

I have a workbook with a number of sheets and I have been slowly working towards automating opening files downloaded daily and copying the data across to the various sheets. I have 2 parts of the equation working well, but am unsure of how to combine them.

Here is what I have for opening all the files in 1 location.

Sub Open_All_Files_LTD()
Dim sFil As String
Dim sPath As String

sPath = "/Volumes/DOCUMENTS/Horse/Football Advisor/New Role/Predictology/Lay The Draw/" 'location of files
ChDir sPath
sFil = Dir("")
Do While sFil <> ""
Workbooks.Open FileName:=sPath & sFil
sFil = Dir
Loop
End Sub

This works really well to open all of the files in the specified folder one by one.

In another thread, a very kind and talented Excel guru shared some code allowing me to copy all the data in the active sheet to the desired location, including adding the source filename to column A.

Sub LAY_THE_DRAW_Weekly()
'
' Predictology
' This macro copies and pastes to the Predictology file
'
    Dim srcWB As Workbook
    Dim destSht As Worksheet, srcSht As Worksheet
    Dim destRng As Range
    Dim destLRNew As Long
        
    Set destSht = Workbooks("Predictology-Reports Football Advisor.xlsx").Sheets("Lay The Draw")

    Set srcWB = ActiveWorkbook
    Set srcSht = ActiveSheet
    With srcSht
        With .Cells(1).CurrentRegion
            .HorizontalAlignment = xlCenter
            .Offset(1).SpecialCells(xlCellTypeVisible).Copy
        End With
    End With

    With destSht
        Set destRng = .Range("B" & Rows.Count).End(xlUp).Offset(1)
        destRng.PasteSpecial xlPasteValues
        destLRNew = .Range("B" & Rows.Count).End(xlUp).Offset(1).Row
        destRng.Resize(destLRNew - destRng.Row).Offset(0, -1).Value = Replace(srcWB.Name, ".csv", "")
    End With
    
    Application.CutCopyMode = False
End Sub

So I have both elements working well on their own. The first code opens each file one by one and the second code copies all of the data sans the header row into the correct location. How is it possible to combine the two sets of code to achieve the ultimate aim, which is to firstly open the first file in the location, copy the data, close that file and open the next and so on?

Thanks so much in advance.

 

[honkin]

Hi Michael
Yes, you're right. I changed it to this instead and it attempts to close the files after each is done. The only things is each file brings up a save dialogue box. Is it possible to have the response be save when this dialogue box appears?

86 that Micael. Some research brought up

srcWB.Close SaveChanges:=1

And it is all good.

Thanks so much for all your help

 

[Michael M]

Use

srcWB.Close SaveChanges:=True

 

[honkin]

Use

srcWB.Close SaveChanges:=True

Excellent Michael. Thanks so much