Loop Through To Enter Data In Multiple Closed Workbooks

[Tristanfrontline]

Hi
I currently use a macro where I enter the location ofa bunch of workbooks and it automatically changes a specific cell to what I require. The code is -

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myrange As Range
    Dim Lastrow As Long
    Dim sFolder As String
    
    'folder where workbooks are stored
     'change as required (Ensure that the end of your link has the backslash otherwise code wont work)
    sFolder = "C:\\"
   
    Lastrow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row + 1
    If Lastrow < 4 Then Lastrow = 4
    Application.ScreenUpdating = False
    Set myrange = Me.Range("D13:D13" & Lastrow)
    If Not Intersect(Target, myrange) Is Nothing Then
        UpdateFilesInFolder Target, sFolder
    End If
    Application.ScreenUpdating = True
End Sub

Sub UpdateFilesInFolder(ByVal Target As Range, ByVal sFolder As String)
    Dim strFile As String
    
    strFile = Dir(sFolder & "*.xlsx", vbNormal)

    Do While strFile <> ""
        If strFile <> ThisWorkbook.Name Then
        Set wb = Workbooks.Open(sFolder & strFile, ReadOnly:=False)
        
        With wb
            .Worksheets("Total Quantities").Range(Target.Address).Value = Target.Value
            .Close True
        End With
        End If
        strFile = Dir
    Loop
End Sub

This works well but when I have sub folders it will not search through those folders and update any workbooks which are in those. I am also wanting to make it so instead of entering the location manually I would just click a button instead and browse the directory the excel workbooks are stored. What coding do I need to change so that it will work in the way I am describing. I hope this makes sense. Any help will be greatly appreciated