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]

...... after these changes, the MsgBox shows nothing .. it's just a blank box

 

[Fluff]

What values do you have in K4, L4, M4 & N4 on sheet Information?

 

[Mummafrog]

K4 is the static directory path for the chosen client
L4 is the month they have chosen
M4 is the year they have chosen
N4 is the pattern that clients files follow


I have just had a thought ... the actual cells contain a formula to find that information.. would that matter? I might try adding a copy/paste/values tomorrow to see if that makes a difference.

 

[Fluff]

The fact that they are formulae shouldn't make any difference.
In K4 S:\Client\Operations\DCR\
In L4 4 Oct\
In M4 2017\
In N4 (HO)21

Is this correct? & are the Month & Year values or formatted dates?

 

[Mummafrog]

Yes, that is correct .. They are values

 

[Fluff]

In that case could you repost the code that you are using, as the message box should be showing those values

 

[Mummafrog]

Don't have access to the work computer from home, will post in the morning. Thanks again

 

[Mummafrog]

OK. Here it is

Sub MergeAllWorkbooks()

    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkSht As Worksheet
    Dim SourceRange As Range
    Dim DestRange As Range
    Dim lblDir As String, lblMonth As String, lblYear As String, lblFile As String
    
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    
    With Workbooks("thirdtest.xlsm").Worksheets("Information")
        lblDir = .Range("K4").Value
        lblMonth = .Range("L4").Value
        lblYear = .Range("M4").Value
        lblFile = .Range("N4").Value
    End With
    
    FolderPath = lblDir & lblMonth & lblYear
    
    NRow = 1
    
    FileName = Dir(FolderPath & lblFile & "*" & ".xlsm")
    MsgBox FileName
    Do While FileName <> ""
        Workbooks.Open (FileName)
        Set WorkSht = ActiveWorkbook.Sheets("Discounted")
        SummarySheet.Range("A" & NRow).Value = FileName
        Set SourceRange = WorkSht.Range("A1:N" & WorkSht.Range("N" & Rows.Count).End(xlUp).Row)
        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
        ActiveWorkbook.Close savechanges:=False
        FileName = Dir()
    Loop
    
    SummarySheet.Columns.AutoFit
End Sub


[code]

 

[Fluff]

Try changing the message box to this

    MsgBox FolderPath & vbLf & FileName

& see what that gives

 

[Mummafrog]

Will do. It's the weekend, so I won't get to it till Monday though.