Updating Macro to use Folder path specified by user

[JustRob]

I altered this code and its working as is, but I'm trying to make an update to it and can't get it to work. Update runs a separate macro that allows a user to select a folder path and stores the folder path in a cell (cell L1). Instead of this piece of code having the file path written in as shown, I need it to reference Cell L1 and select that folder path. This current macro I'm trying to update goes to the folder path shown and pulls a pre-determined piece of every excel file in the folder and drops it into my new Master Sheet - basically consolidates all the data I'm looking for into 1 sheet.

Can I get help to re-write the section of this macro (bold below) that has the folder path written in, to instead use the folder path in cell L1?

Sub Consolidate()

Dim fName As String, fPath As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet

'Setup
    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now
  
    Set wsMaster = ThisWorkbook.Sheets("Data Pull")    'sheet report is built into

With wsMaster
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        .UsedRange.Offset(1).EntireRow.Clear
        NR = 2
    Else
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    End If

'Path and filename (edit this section to suit)
    fPath = "Q:\QM\Sample Files\"           
    On Error GoTo 0
    fName = Dir(fPath & "*.xlsm*")        'listing of desired files, edit filter as desired

'Import a sheet from found files
    Do While Len(fName) > 0
        If fName <> ThisWorkbook.Name Then              'don't reopen this file accidentally
            Set wbData = Workbooks.Open(fPath & fName)  'Open file

        'This is the section to customize, replace with your own action code as needed
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
            Range("B11:I149" & LR).Copy .Range("A" & NR)
            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            End If
        fName = Dir                                       'ready next filename
    Loop
End With

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

fpath = .Range("L1").Value
If Right(fpath, 1) <> "\" Then fpath = fpath & "\"

 

[JustRob]

Hi & welcome to MrExcel.
How about

fpath = .Range("L1").Value
If Right(fpath, 1) <> "\" Then fpath = fpath & "\"

Thanks Fluff for the response. I tried using Range to call on value, but was unsuccessful. I attempted adding in your piece and received a Run-time error '52', Bad file name or number. It seems it gets passed the fpath = .Range("L1").Value piece, but gets hung up on the next line: fName = Dir(fpath & "*.xlsm*"). The path in L1 looks the same as in the working Macro that already has the path written in, so the folder path shouldn't be the issue. Any other suggestions?

 

[Fluff]

If you add this before the Dir line what does it say

MsgBox fpath

 

[JustRob]

If you add this before the Dir line what does it say

MsgBox fpath

You're on point Fluff. Extra character was being drawn in, corrected and your 2 lines that you suggested worked like a charm. Beautiful! Thanks a million!!!

 

[Fluff]

Glad it's sorted & thanks for the feedback.