Macro Not Working

Question

Good Evening

I have used the following macro, obtained from the internet,  many times to consolidate multiple workbooks in a folder into a master workbook:

Sub ConslidateWorkbooks()

'Created by Sumit Bansal from https://trumpexcel.com

Dim FolderPath As String

Dim Filename As String

Dim Sheet As Worksheet

Application.ScreenUpdating = False

FolderPath = Environ("userprofile") & "DesktopTest"

Filename = Dir(FolderPath & "*.xls*")

Do While Filename <> ""

 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

 For Each Sheet In ActiveWorkbook.Sheets

 Sheet.Copy After:=ThisWorkbook.Sheets(1)

 Next Sheet

 Workbooks(Filename).Close

 Filename = Dir()

Loop

Application.ScreenUpdating = True

End Sub

Today, rather than producing a master workbook, it produced a combined workbook of two blank worksheets Sheet1 and Sheet1(2).  I am a vba newbie but have checked online suggestions (trust center settings, saved as .xls. or .xlsx. and on an on.)  I have spent most of the day off and on trying to get this vba to work so that I could avoid a 20 minute manual process!

I wondering if anyone has a recommendation for a step based procedure to understand why a macro that has run many times now fails to run successfully.

Thank you.

Al

Today, t

Answer 1

Hi Al

The line in your code 

FolderPath = Environ("userprofile") & "DesktopTest"  would never works

It should be

FolderPath = Environ("UserProfile") & "\Desktop\Test"

It is shown on the website

Later they clarify

I made some additions to the code, I switched off the alerts to avoid prompting messages

I tested on my side with a Test folder on my desktop and works perfectly

Follow the instructions above if your folder is on a different path

*********************************************************************************************

Sub ConslidateWorkbooks()

'Created by Sumit Bansal from https://trumpexcel.com

Dim FolderPath As String

Dim Filename As String

Dim Sh As Worksheet

Application.ScreenUpdating = False

Application.DisplayAlerts = False

FolderPath = Environ("UserProfile") & "\Desktop\Test"

Filename = Dir(FolderPath & "*.xls*")

Do While Filename <> ""

         Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

         For Each Sheet In ActiveWorkbook.Sheets

                Sheet.Copy After:=ThisWorkbook.Sheets(1)

         Next Sheet

         Workbooks(Filename).Close

         Filename = Dir()

Loop

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "Job Done"

End Sub

***********************************************************************************************

I hope this helps

Regards

Jeovany

Answer 2

Hi

Thanks for responding.  I just ran the following that you recommended:

Sub ConslidateWorkbooks()

'Created by Sumit Bansal from https://trumpexcel.com

Dim FolderPath As String

Dim Filename As String

Dim Sh As Worksheet

Application.ScreenUpdating = False

Application.DisplayAlerts = False

FolderPath = Environ("UserProfile") & "\Desktop\2018_SERFF - Copy"

Filename = Dir(FolderPath & "*.xlsx*")

Do While Filename <> ""

         Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True

         For Each Sheet In ActiveWorkbook.Sheets

                Sheet.Copy After:=ThisWorkbook.Sheets(1)

         Next Sheet

         Workbooks(Filename).Close

         Filename = Dir()

Loop

Application.ScreenUpdating = True

Application.DisplayAlerts = True

MsgBox "Job Done"

End Sub

It returned the "Job Done" message but did not consolidate any files.  BTW, I think I mentioned that I have run this macro successfully many times.  BTW2, your comments about "Desk File" are correct.  On the website, they recommend inserting local information to retrieve the files which, as I said, I have done many time.   BTW3, I ran the macro above both as an .xls and .xlsx versions and it returned "Job Done" with nothing else.

Trust me when I say I spent a lot of time today trying to understand what has changed.  For example, I ran the macro that I have had previous success with on both of my computers and the macro only returned partial information once.  From there on I couldn't get it to run successfully again.

I wondering if I turned off something on my computer or if recent Windows 10 updates might have changed something?  Could be something a vba newbie misses?

Genuinely appreciate your help and any thoughts that you can share.

Al

Answer 3

Well 

You might need to reinstall your Office, or pass this thread to the developers.

Like I said,

On my side it works fine

I wish I could help you more.

Regards

Jeovany

Answer 4

Thank you for weighing in.  Will post any changes.

Thanks again.

Al

Answer 5

Did you ever resolve this issue? I am having a similar issue. The "Filename = Dir()" stopped working for me, when it was working just fine in another part of my code yesterday.