How best to copy a hidden sheet with a multiple formulas to create a multiple new sheets

[PaulskinX1]

Dear all,

I created a procedure which does this:

- copy one hidden sheet with that contains a table with a significant number of formulas ( table size is 100 R x 11 C , about 8 column includes formulas (some of them are quite advanced)) and create new identical sheet but with a different name indicated by the user in different section of the model (essentially in another table).

I am using an array to pick up the sheets names (strings are appended to array from a named range 'rng_Target' which user of the tool provides) to be created , that are provided by the user (at the moment maximum 10 new sheets can be created.

My problem is the time it takes for this procedure to create those sheets - it takes really long time - up to 5min.

Is there anything obviously wrong with my code please? File: Create Copy of sheet-code 1

I have to say , the previous version of the code used (which does not involve arrays) , seemed to be working much faster (for some of the users) ; which i don't understand - see File: Create Copy of sheet-code 2

How can i change my existing code to run it faster?


Many thanks for any help!

 

[CephasOz]

The big difference between your two subs is that one has the Application settings changed to speed things up and the other does not. If, before you start creating copies, you use the code:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.StatusBar = False

Then, after the copies have been created, re-set the settings like so:

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = True

... you will find that it all happens a lot faster.

 

[PaulskinX1]

The big difference between your two subs is that one has the Application settings changed to speed things up and the other does not. If, before you start creating copies, you use the code:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.StatusBar = False

Then, after the copies have been created, re-set the settings like so:

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = True

... you will find that it all happens a lot faster.

HI @CephasOz ,

Thanks for your answer however you completely misread my original post. I thought I was pretty clear in my explanation on the original post (however apologies if I wasnt)

It is the code nr 2 (the shorter one , which does not include Application.Calculation line ) which runs much faster than code nr 1. And this is what confuses me!

So once again it is code nr 2 (shorter version) which runs much faster. Code nr 1 (with arrays and Application.Calculation line) runs significantly slower.

 

[CephasOz]

I re-wrote the code for the first one. I think that using "On Error Resume Next" can disguise problems too easily. Try removing it so that you can see that everything is happening as it should. Anyway, here's the re-written code for contrast. Note that forcing the sub to reset the Application settings even when there's an error makes life so much easier because you're not left with a blank screen and wondering what happened.

Sub Convertlnput() ' wrzuc input od uzytkownika z named range w tablice
    Dim i As Integer
    Dim myArray() As Variant
    Dim strErrMsg As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Disp1ayA1erts = False
    Application.StatusBar = False
    Application.Calculation = xlCalculationManual
    '
    strErrMsg = vbNullString
    On Error GoTo Err_Exit
    myArray = Range("rng_Target").Value
    wsToDuplicate.Visible = xlSheetVisible
    For i = LBound(myArray) To UBound(myArray)
        If (Not IsEmpty(myArray(i, 1))) Then
            wsToDuplicate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            ActiveSheet.Name = myArray(i, 1)
        End If
    Next i
Housekeeping:
    Erase myArray() ' wyczysc array
    wsToDuplicate.Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Disp1ayA1erts = True
    Application.StatusBar = True
    Application.Calculation = xlCalculationAutomatic
    If (Trim(strErrMsg) <> vbNullString) Then
        MsgBox strErrMsg, vbExclamation + vbOKOnly, "Error"
    End If
    Exit Sub
Err_Exit:
    strErrMsg = Err.Number & Space(1) & Err.Description
    Err.Clear
    Resume Housekeeping
End Sub

 

[PaulskinX1]

I re-wrote the code for the first one. I think that using "On Error Resume Next" can disguise problems too easily. Try removing it so that you can see that everything is happening as it should. Anyway, here's the re-written code for contrast. Note that forcing the sub to reset the Application settings even when there's an error makes life so much easier because you're not left with a blank screen and wondering what happened.

Sub Convertlnput() ' wrzuc input od uzytkownika z named range w tablice
    Dim i As Integer
    Dim myArray() As Variant
    Dim strErrMsg As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Disp1ayA1erts = False
    Application.StatusBar = False
    Application.Calculation = xlCalculationManual
    '
    strErrMsg = vbNullString
    On Error GoTo Err_Exit
    myArray = Range("rng_Target").Value
    wsToDuplicate.Visible = xlSheetVisible
    For i = LBound(myArray) To UBound(myArray)
        If (Not IsEmpty(myArray(i, 1))) Then
            wsToDuplicate.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
            ActiveSheet.Name = myArray(i, 1)
        End If
    Next i
Housekeeping:
    Erase myArray() ' wyczysc array
    wsToDuplicate.Visible = xlSheetVeryHidden
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Disp1ayA1erts = True
    Application.StatusBar = True
    Application.Calculation = xlCalculationAutomatic
    If (Trim(strErrMsg) <> vbNullString) Then
        MsgBox strErrMsg, vbExclamation + vbOKOnly, "Error"
    End If
    Exit Sub
Err_Exit:
    strErrMsg = Err.Number & Space(1) & Err.Description
    Err.Clear
    Resume Housekeeping
End Sub

Thanks you @CephasOz for taking time and looking into my problem. Your code is more elegant for sure, although i get error message at line:

[QUOTE]
Application.EnableEvents = True
[/QUOTE]

.

My problem is that your code is running only marginally faster than mine original one.
My vba code copies 1 hidden tab and creates a number of new sheets on that basis with a different name (name is specified by the user in a section of the excel workbook). The problem is that the hidden tab (that one which is copied) has got a significant number of formulas (about 100 rows x10 columns). It means creation of the new sheets take a lot of time - about 25 second each. I need to have calculation on as the results of formulas in each new tab are required for a further processing.

How can i speed up my vba procedure (i can add i already use Application. property with a various actions switched off) OR is there no change to improve speed if you worksheets are so heavily formula infested.

 

[CephasOz]

Hi @PaulskinX1. If the line

Application.EnableEvents = True

is throwing an error, try deleting that line entirely, then re-type it exactly as it was.
As to having Application.Calculation on or off while running the sub, it's better to have it off, then turn it on at the end (just as it is in the code). You can always add a line

Application.Calculate

before the line

Exit Sub

to force recalculation.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Is there a way to speed up my code which copy a heavily formula infested worksheet and creates a multiple new sheets? - OzGrid Free Excel/VBA Help Forum
and Is there a way to speed up my code which copy a heavily formula infested worksheet

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[PaulskinX1]

@Fluff , same question also placed in here:

Is there a way to speed up my code which copy a heavily formula infested worksheet

and creates a multiple new sheets? (cont'd title question) My vba code copies 1 hidden tab and creates a number of new sheets on that basis with a different name (name is specified by the user in a section of the excel workbook). The problem is that the hidden tab (that one which is copied)...

www.vbaexpress.com