Duplicating Macro slow

[JVID]

I have a macro that duplicates a template several times. I turn off calculations and when I'm about 100 of 800 worksheets in it gets VERY slow.

Any advice? Anything I can do?

Thanks,

John

 

[wigi]

Did I read correctly that you have 800 worksheets in the same file?
If not, please explain greater detail please.

 

[JVID]

Yes- I did. It's more like 1000 because I run to scriptsExcel allows you to exceed a limit when you are duplicating.

any insight into how to speed up the macro?

JV

 

[wigi]

1000 sheets to me sound like a recipe for distaster. But in the end, I'm not the one using this
Did you turn off ScreenUpdating as well?
Is your code optimised in the sense that duplicating only 1 sheet is in the best possible way?
Do you free up resources in your code that you do not need anymore?

 

[JVID]

Hey it worked once it finished! haha thanks for the concern.

can you tell me how to do each of those?

J

 

[wigi]

Application.ScreenUpdating = False
'your code
Application.ScreenUpdating = True

 

[JVID]

Thanks for the record it only took about 3 hours so this should help more

 

[wigi]

Thanks for the record it only took about 3 hours so this should help more

Hmmm...

 

[JVID]

Where would this code go in this example

Private Sub indexer()
Dim wSheet As Worksheet
Dim l As Long


l = 1


    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    


    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With


                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet


End Sub

 

[wigi]

Private Sub indexer()
Dim wSheet As Worksheet
Dim l As Long

Application.ScreenUpdating = False

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
    
    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With


                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet


End Sub

Application.ScreenUpdating = True