Combining one worksheet from multiple CLOSED workbooks to one summary page

[Mummafrog]

I need to take the the information on one worksheet from each of (up to) 31 files ... (one per day each month).

Each of these worksheets is formatted exactly the same, and always contains a heading row, but may or may not contain data, dependent on whether certain conditions were met.

I have made an individual worksheet, so that the user can pick the client/month/year of the data required and then simply push a button to receive the data.

The path is dependent on which client is requesting the data, as such I have pointed the path to cells on an information sheet which is populated dependent on the choices made. One such path could be ..

(S:\Client\Operations\DCR\04 Oct\2017\)

All of the files that are required to be merged are in the format (Client date .xlsm)

I have 4 cells on the Information sheet that populate with each part of the path (Client .. Month .. Year .. Pattern)

I have tried many different ways, the most recent follows, and I get no errors, but also no information. A new sheet opens, and sits nicely at A1. What am I doing wrong??

Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range


Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)


lblDir = Workbooks("thirdtest.xlsm").Worksheets("Information").Range("K4").Value
lblMonth = Workbooks("thirdtest.xlsm").Worksheets("Information").Range("L4").Value
lblYear = Workbooks("thirdtest.xlsm").Worksheets("Information").Range("M4").Value
lblFile = Workbooks("thirdtest.xlsm").Worksheets("Information").Range("N4").Value

FolderPath = "lblDir & lblMonth & lblYear"


NRow = 1


FileName = Dir(FolderPath & lblFile)


Do While FileName <> ""

Set WorkBk = Workbooks.Open(FolderPath & FileName)


SummarySheet.Range("A" & NRow).Value = FileName


Set SourceRange = WorkBk.Worksheets(Discounted).Range("A:N")


Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)


DestRange.Value = SourceRange.Value


NRow = NRow + DestRange.Rows.Count


WorkBk.Close savechanges:=False


FileName = Dir()
Loop


SummarySheet.Columns.AutoFit
End Sub


I am willing to totally scrap this and start over if needs be, just need some help Ta

 

[Mummafrog]

No

 

[Fluff]

You're quoting file names like HO (21) 01-10-17, without an extension. Is that how it appears in the message box?
If so do you have this line of code?

 sFname = sPath & "\" & lblFile & [COLOR=#ff0000]"*.xls*"[/COLOR]

 

[Mummafrog]

Sorry, yes the file extension is there

 

[Fluff]

In that case I'm afraid I'm at loss.
The code works fine for me and without access to your system, there's not much more I can do.