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() Loop 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.