VBA not working when cell value holding file path string changed

[Lola223]

Hi,

I am using the following VBA to combine some daily reports into an end of month report. The files in question are all .Csv files.

The user should enter the current months filepath into cell B1. I am finding that this works for the current month ie February 22 then when I change the path in cell B1 to another month, I get an error indicating that the first csv file that the code is looking for is still the first csv file in Feb and therefore the code fails. Can anyone help point me in the direction of where I am going wrong?

Public strPath As Range

Sub CopyRange()

    Application.DisplayAlerts = False

    Application.ScreenUpdating = False

    Dim wkbDest As Workbook

    Dim wkbSource As Workbook

    Set wkbDest = ThisWorkbook

    Dim LastRow As Long

    Set strPath = Worksheets("Combine Dataset").Range("B1")

    strExtension = Dir("*.csv*")

    Do While strExtension <> ""

        Set wkbSource = Workbooks.Open(strPath & strExtension)

        With wkbSource

             LastRow = .ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

            .ActiveSheet.Range("A5:K" & LastRow).Copy wkbDest.Sheets("Banking Combined").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

            .Close savechanges:=False

        End With

        strExtension = Dir

    Loop

    Application.ScreenUpdating = True

End Sub

 

[KOKOSEK]

We do not know how's your folder/files are organised but try:

strExtension = Dir(strPath & "*.csv")

 

[Lola223]

Thank you very much, that did the trick!