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

 

[Jon Peltier]

Well, you didn't share the error message or indicate what was highlighted when the error occurred.

First thing, though, I think Pth needs a trailing backslash.

 

[MrMan32]

Thanks, Jon. I added the trailing backslash and it seems the problem is with fname = Dir(Pth & "*xlsm*"). Is it possible the excel the fact that the workbooks are macro enabled excel files is causing the issue?

 

[Fluff]

Is the path correct, or should it be something like
C:\Users\Fluff\Desktop\Client Folder\2018 Clients\

 

[MrMan32]

Thanks, Fluff.

It doesn't seem to be having issues with the path, but I copied and pasted the path location directly from the folder info so that it now reads as
Pth = "C:/Users/MrMan32/Desktop/Client Folder/2018 Clients/

Error seems to still be with
fname = Dir(Pth & "*xlsm*")

 

[Fluff]

If you step through the code using F8 what happens?

 

[MrMan32]

No issues until I get to
fname = Dir(Pth & "*xlsm*")

Then I get -

Run-time error '76':
Path not found

 

[Fluff]

In that case there is a problem with the path.
Open a file in the relevant folder & with that being the active workbook run this

Sub Chk()
Debug.Print ActiveWorkbook.Path
End Sub

If you then look in the immediate window (Ctrl G will bring it up, if it's not visible) & you should see something like C:\Users\Fluff\Documents\Excel files

Simply copy that & paste it into your code adding the final separator

 

[MrMan32]

Thanks Fluff, but nothing happened when I ran the code.

'm using a Mac so I tired to lookup the equivalent of Ctrl G shortcut - I think it's F5 but I didn't see a path indicated in the "Go To" dialog box.

 

[Fluff]

In that case in the VB editor, click view > Immediate window.

Also when using a Mac it's best to say so as there are differences between PC & Mac VBA.
Mac file paths are different to PC ones.