Simple Task - copy the sheets present in multiple workbooks from an input folder into a master work

Occasional Visitor

I am getting an automation error when I include the AssessmentFile.close statement. Also this code just copies the worksheets in the last workbook of input folder.

 

Set AssessmentFile = Workbooks.Open(FileName:=myPath & myFile)
    For Each ws In AssessmentFile.Worksheets
        ActiveSheet.Name = Replace(myFile, myExtension, "")
    Next ws
    
    
    
    **AssessmentFile.Close**
    myFile = Dir
Loop

outputFile = outputFilePath & outputFileName
    strFileExists = Dir(outputFile)
 
   If strFileExists = "" Then
'**getting an automation error in this step
        AssessmentFile.SaveAs FileName:=outputFile, FileFormat:=xlWorkbookNormal**
    Else
        If IsFileOpen(outputFile) Then
        GoTo CloseTheOpenOutputFile
        Else: Kill outputFile
        DoEvents
        AssessmentFile.SaveAs FileName:=outputFile, FileFormat:=xlWorkbookNormal
        End If
        DoEvents
    End If```

 

https://stackoverflow.com/questions/71639741/trying-to-copy-multiple-workbooks-in-an-input-folder-to...

 

Any help is much appreciated.

0 Replies