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
    myFile = Dir

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


Any help is much appreciated.

0 Replies