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 Heather

I never got it to work in that particular folder.  I created a new folder with the same workbooks and the macro ran.  I did do some cleaning up of workbooks in the folder by eliminating named ranges.  

Can't offer much more because it was a surprise to me when it finally ran.

Hope this helps.

Al