I am new to VBA coding, but I have repetitive tasks at work that are tedious and I would like to automate. One such tasks is taking ~60 files one for each county in my area and adding an instruction page (which I have done successfully with vba) then adding 4 columns A2:F2 with highlighted headers from my source workbook with the VBA code and inserting this range of 4 columns into each of the 60 workbooks on the second tab.

Sub CopyDataToMultipleWorkbooks()
Dim sourceWB As Workbook
Dim sourceWS As Worksheet
Dim destFolder As String
Dim destFilename As String
Dim destWB As Workbook
Dim destWS As Worksheet
Dim lastRowSource As Long
Dim lastRowDest As Long
Dim i As Long

' Set the source workbook and worksheet
Set sourceWB = ThisWorkbook
Set sourceWS = sourceWB.Sheets(2)

' Set the destination folder path
destFolder = "K:2024 CycleData Management"

' Find the last row of data in the source worksheet
lastRowSource = sourceWS.Cells(sourceWS.Rows.Count, "A").End(xlUp).Row

' Loop through each file in the destination folder
destFilename = Dir(destFolder & "*.xlsx")
Do While destFilename <> ""
    ' Open the destination workbook
    Set destWB = Workbooks.Open(destFolder & destFilename)
    
    ' Set the destination worksheet
    Set destWS = destWB.Sheets(2)
    
    ' Find the last row of data in the destination worksheet
    lastRowDest = destWS.Cells(destWS.Rows.Count, "A").End(xlUp).Row
    
    ' Copy data from source worksheet to destination worksheet
    sourceWS.Range("A2:F2" & lastRowSource).Copy destWS.Range("A" & lastRowDest + 1)
    
    ' Close and save the destination workbook
    destWB.Close SaveChanges:=True
    
    ' Get the next filename in the folder
    destFilename = Dir
Loop


MsgBox "Data has been copied to all destination workbooks in the folder.", vbInformation

End Sub

Results: I get no errors, it looks like it is going through the looping with the opening and closing of the files, I get the task finished message, but when I open up each of the 60 files it as if nothing was added or saved.