Buggy Sheet Consolidation Behaviour

Hello all,

For some time now I've been happily using some VBA code provided by the brilliant JBeaucaire to consolidate data from several sheets into one.

I now need to create a second sheet to do something very similar, but for some reason it only takes data from two of the five sheets, and not even all of that. The VBA to capture and paste the data seems the same in both cases (apart from target cells and subsequent offset data grabs), and I've pasted it at the end of this post.

I've attached an example of the workbook I'm working on:
- The sheet that works properly is 'Summary' and the code is in 'ModuleConsolidateSheets'.
- The sheet that doesn't work properly is 'UPS P&L' and the code is in 'ModulePLConsolidateSheets'
The code is triggered by selecting the respective workbooks.

Does anyone have any idea why the latter returns so much less data than the first?!

'Process each data sheet
    For Each ws In Worksheets
        If ws.Name = "OMS MON" Or ws.Name = "OMS TUE" Or ws.Name = "OMS WED" Or ws.Name = "OMS THUR" Or ws.Name = "OMS FRI" Then
            LR = ws.Range("D" & ws.Rows.Count).End(xlUp).Row
            
        'customize this section to copy what you need
            Set CpyRNG = ws.Range("D6:D" & LR).SpecialCells(xlConstants)

Then CpyRNG.Copy cs.Range("A" & NR) on one and
        CpyRNG.Copy cs.Range("H" & NR) on the other.

Then each has a series of CpyRNG.Offset lines to grab nearby data.

Hi shockeroo

change this

 NR = cs.Range("B" & cs.Rows.Count).End(xlUp).Row + 1

to

 NR = cs.Range("D" & cs.Rows.Count).End(xlUp).Row + 1