Code runs when debbuging (F8) but doesn't run normally (F5)

Question

The following code is triggered when the user press a button from my add-in tab:

Sub OnActionListaMarkowitz(control As IRibbonControl)

    GeneratesWorkbook

End Sub

In a different modules I have a sub to create a new workbook and copy the sheets in my Xlam file to the new workbook.

Sub GeneratesWorkbook()

On Error GoTo LabelErro


Set PlanilhaMarkowitz = Workbooks.Add

Dim currentSheet As Worksheet
Dim sheetIndex As Integer
sheetIndex = 1

    'Changes xlam property to false so I can copy Xlam sheets to new wb
    ThisWorkbook.IsAddin = False
    ThisWorkbook.Activate

    'Copy Xlam's sheets to new workbook 
    Sheets("Hidden").Visible = True 'this workbook was very hidden so I make it visible to copy
    Sheets("Hidden").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)
    Sheets("Hidden").Visible = xlVeryHidden

    Sheets("Calculos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

    Sheets("Pesos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

    Sheets("Atributos").Visible = True 'this workbook was very hidden so I make it visible to copy
    Sheets("Atributos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)
    Sheets("Atributos").Visible = xlVeryHidden

    Sheets("Correl").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

    Sheets("Fronteira").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

    ThisWorkbook.IsAddin = True

    Exit Sub

LabelErro:

        ThisWorkbook.IsAddin = True

End Sub

The odd part of it is that if I execute this code by pressing F8 (step by step) It works perfectly. But If I run this code normally or using F5 it only copy the worksheet("Hidden").

I've set a breakpoint at

Sheets("Hidden").Visible = True

Pressing F8 till the end of the two subs makes it work. Pressing F5 doesn't.

Have you tried throwing a msgbox / debug.print into your code right after the point where you think it fails, to see how far it gets on its own? ie: put it right before 'EXIT SUB'. Does it make it there, or does it error out somewhere before that? Have you tried running it by F8 again, or did it only run fine once? Maybe after you ran it the first time something changed? — Grade 'Eh' Bacon, Jul 22 '15 at 20:00
I've run it several times it always works with F8 never with F5. The code dies at: Sheets("Calculos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex) — Ihidan, Jul 22 '15 at 20:04

score 1 · Accepted Answer · edited May 23 '17 at 10:24

Don't rely on .Activate. The action of copying a worksheet to the new workbook is changing the activeworkbook.

Sub GeneratesWorkbook()

    On Error GoTo LabelErro


    Dim PlanilhaMarkowitz As Workbook
    Set PlanilhaMarkowitz = Workbooks.Add

    Dim currentSheet As Worksheet
    Dim sheetIndex As Integer
    sheetIndex = 1

    With ThisWorkbook
        'Changes xlam property to false so I can copy Xlam sheets to new wb
        .IsAddin = False

        'Copy Xlam's sheets to new workbook
        .Sheets("Hidden").Visible = True 'this workbook was very hidden so I make it visible to copy
        .Sheets("Hidden").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)
        .Sheets("Hidden").Visible = xlVeryHidden

        .Sheets("Calculos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

        .Sheets("Pesos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

        .Sheets("Atributos").Visible = True 'this workbook was very hidden so I make it visible to copy
        .Sheets("Atributos").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)
        .Sheets("Atributos").Visible = xlVeryHidden

        .Sheets("Correl").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

        .Sheets("Fronteira").Copy Before:=PlanilhaMarkowitz.Sheets(sheetIndex)

        .IsAddin = True

    End With

    Exit Sub

LabelErro:

    ThisWorkbook.IsAddin = True

End Sub

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

It is working now. I am still curious about why this happens. — Ihidan, Jul 22 '15 at 20:13
All of the `Sheets` references relied upon whatever was the [ActiveWorkbook property](https://msdn.microsoft.com/en-us/library/office/ff821871.aspx) to determine what their parent workbook was. As soon as you copied the first, the ActiveWorkbook was changed to the one that just received the first copied worksheet. This switch in the ActiveWorkbook is the default behavior. Subsequent attempts to copy worksheets were looking in the new target workbook for the worksheet to copy. Using the [With ... End With statement](https://msdn.microsoft.com/en-us/library/wc500chb.aspx) defined the parent. — , Jul 22 '15 at 20:21

Code runs when debbuging (F8) but doesn't run normally (F5)

1 Answers1