• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Subscript out of range


I have run VBA code to consolidate all files into each sheet in one master file.
It works perfectly when i test few files, but when i start with many files, error message alert as below:

Can you please help to comment on below code:

>>> use code - tags <<<
Sub ConsolidateBPbyccy()
Dim FolderPath As String
Dim Filename As String
Dim Sheet As Worksheet
Application.ScreenUpdating = False
FolderPath = ActiveWorkbook.Path & "\"
Filename = Dir(FolderPath & "*.xlsx*")
D = 1
Do While Filename <> ""
    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
' For Each Sheet In ActiveWorkbook.Sheets
Sheets(2).Copy After:=ThisWorkbook.Sheets(D)
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True

' Next Sheet
Workbooks(Filename).Close Savechanges:=False
ActiveSheet.Name = Range("B4").Value
Filename = Dir()
  D = D + 1
Application.AskToUpdateLinks = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Thanks for help.
Last edited by a moderator: