Help with loop VBA

[MrMan32]

Hello,

I'm new to the forum so thank you in advance for your help. I'm trying to use the loop function to extract and aggregate data across clients and can really use some help with my VBA code - I'm not sure where I am going wrong.

Here is what I have and am trying to do -
1) I have about 75 excel files in a folder called "2018 Clients"
2) The path to the folder is simply "Desktop\Client Folder\2018 Clients"
3) Each excel file has roughly 15 worksheets. The final worksheet is called "Summary"
4) I am trying to copy data (cells A1:E20) from every "Summary" worksheet in the folder to a single master worksheet to run data analysis.

Here is the code I'm using. I'm not getting any errors when I run the macro but the data is not populating. Any help is greatly appreciated!

Sub CombineWbks()


Dim Pth As String
Dim MstSht As Worksheet
Dim fname As String
Dim Rng As Range

Application.ScreenUpdating = False


Pth = "C:\Desktop\Client Folder\2018 Clients"
Set MstSht = ThisWorkbook.Sheets("Master Summary")
fname = Dir(Pth & "*xlsm*")
Do While Len(fname) > 0
Workbooks.Open (Pth & fname)
With Workbooks(fname)
.Sheets("Summary").Range("A1:E20").Copy MstSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Close , False
End With
fname = Dir
Loop


End Sub

 

[MrMan32]

Thanks, Fluff. And sorry about that.

I think that solved the path problem. Now when I run the code, I don't get any error messages but nothing happens.

 

[Fluff]

Do any files get opened?

 

[MrMan32]

I don't think so. Nothing opens on my screen.

Again, I'm new to loop but I was assuming that the spreadsheets would all be "opened" behind the scenes without me actually seeing them on my computer.

 

[Fluff]

Try stepping through the code again using F8, what happens?

 

[MrMan32]

It just goes through each step. No issues, but nothing happens

 

[Fluff]

In that case what does this say in the immediate window

Sub chk()
Debug.Print Sheets("Master Summary").Range("A" & Rows.Count).End(xlUp).Offset(1).Row
End Sub

 

[MrMan32]

"2"

 

[Fluff]

In that case the only thing I can think of, if the other workbooks are opening, is that there is no data in A1:E20, to copy over

 

[MrMan32]

There is definitely data in those cells. Any other thoughts? Any other ideas on how to copy and paste data from numerous spreadsheets (the same range, the same sheet name) into a single master sheet?

 

[Fluff]

If it's opening all the other workbooks, then I've no idea why it's not working.