(Solved) – Macro Fails after Multiple Iterations


I’m using the macro below to import some sheets into a file, then saving the file with a new name in a different location. The macro works well for about 40 iterations (it’s set to loop through a few hundred folders and combine the files located in each one separately), and then it fails. Sometimes I get an ‘out of memory’ error (which can’t possibly be the case- I’m working on a PC that has 32GB RAM and these files aren’t big), sometimes the macro just randomly exits with no error, and sometimes I receive an error on this line: Set Wkb3 = Workbooks.Open(filename:=Path & "" & filename)

Here’s my macro:

Option Explicit
Sub CombineFiles()
Call NewBook 'this marco creates a new file that will hold the imported sheets

Dim Wkb1 As Workbook 'Wkb with Macro
Set Wkb1 = ThisWorkbook

Dim Aname As String
Aname = Wkb1.Sheets(1).Range("A1").Value & "Master FileMaster File.xlsx" 'cell A1 holds the path for each individual folder that holds files that need to be combined

Dim Wkb2 As Workbook 'MasterBook
Set Wkb2 = Workbooks.Open(filename:=Aname)
Dim Wkb3 As Workbook 'DataSource
Dim ws1  As Worksheet 'Wkb with Macro
Set ws1 = Wkb1.Worksheets(1)
Dim ws3  As Worksheet 'DataSource

Dim MyOldName As String
MyOldName = Wkb2.FullName

Dim Path As String
Path = ws1.Range("A1").Value

Dim filename As String
filename = Dir(Path & "*.xlsx", vbNormal)

Dim Path2 As String
Dim filename2 As String
Path2 = Path & "Master File"

 Do Until filename = ""
        Set Wkb3 = Workbooks.Open(filename:=Path & "" & filename)
        For Each ws3 In Wkb3.Worksheets
            ws3.Copy after:=Wkb2.Sheets(Wkb2.Sheets.Count)
        Next ws3
        Wkb3.Close False
        filename = Dir()
Application.DisplayAlerts = False
filename2 = Wkb2.Worksheets(2).Range("A2").Text
        Wkb2.SaveAs filename:=Path & filename & ".xlsx"
        Wkb2.Close True
        Kill MyOldName
        Call KillFiles
Application.DisplayAlerts = True
End Sub

Any help would be appreciated.

Leave a Reply

Your email address will not be published. Required fields are marked *