Workbooks created and stored in dictionary - slow processing

[Jgiordano]

So here my code. Basically, it creates around 80 workbooks and keep it opened in memory for further purpose. I use a dictionary to be able to loop in those workbooks to distribute reports and then loop again to save them all once the report process is done. I posted only the workbooks creation part, and it takes forever to execute !

For Each nom In Range("nomgestionnaire")
    If nom.Value <> "" Then
        Set book = Workbooks.Add()
        book.Windows(1).Visible = False
        Classeurs.Add nom.Value, book
        
        'Delete all other sheets except Sheet1
        Application.DisplayAlerts = False
        On Error Resume Next
        For Each sh In book.Worksheets
            If sh.Name <> "Sheet1" Or sh.Name <> "Feuil1" Then
                sh.Delete
            End If
        Next
        
        Application.DisplayAlerts = True
    End If

Next

This code is pretty simple that's why i wonder why it takes so long to execute.

Thanks

 

[Norie]

You could get rid of the part deleting sheets if you created single sheet workbooks with this.

Set book = Workbooks.Add(xlWBATWorksheet)

By the way, why are you addding the workbooks to a dictionary?

 

[Jgiordano]

Thanks for that tip ! i will modify it.
Honestly, I use dictionary because i find it easier to use than collections and that i don't know other way to list workbooks and refer to it easily.