MS Excel - VBA (Macros)

I am having problems with the macros I set-up in my spreadsheet. It's not working the way I wanted it to work.

Here's the scenario: I have several excel spreadsheets extracted from my Software and these consists of 8 entities which I wanted to consolidate in one excel file. All sheets, including the consolidated spreadsheet (master file) have the same reference cells.

What I wanted to happen is to lessen the time for consolidation. I wanted to have the files I extracted from the software to go directly to its specified folder, all the while at the same time, populating my master spreadsheet of the date from extracted files. The figures should be consolidated in column B. It's actually two columns master files - 1st column for the description and the 2nd column for the amount.

Below is the macro I set:


Option Explicit

Sub ImportConsolidateData()

' Consolidate Macro
' Import the first sheet from files in a folder
'WCC Database\per entity
Dim strFileName As String, sName As String
Dim MstWb As Workbook, StrWb As Workbook
Dim NR As Long, LR As Long

Set MstWb = ThisWorkbook
ChDir "C:\Users\Desiree\Desktop\WCC Database"
strFileName = Dir("Conso*.xls")

'Import data from found files
Do While Len(strFileName) > 0
'Open book
Set StrWb = Workbooks.Open(strFileName)
'Grab rows and turn on consolidate
Range("B17:B146").Copy
Range("B" & Rows.Count).Consolidate
'Put data in Master workbook
MstWb.Activate
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
NR = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
LR = Range("B" & Rows.Count).End(xlUp).Row
Range(Cells(NR, "A"), Cells(LR, "A")) = sName
'Get next file to open
strFileName = Dir
'Close current store file
StrWb.Close False
Loop

End Sub


I don't know where the error lies.