Excel weird behavior when workbook set to not visible

Question

I'm having the weirdest experience trying to make excel fill in some fields in a template form and then copy/paste it on another WorkSheet. Then it must repeat the process for a limited number of times, always pasting the forms beneath the previous ones. When the page is full, it must copy the header of page #1 and paste it at the top of the next page, then proceed the form copy/paste process.

This routine is part of my VB6 little calculation software I'm currently (trying to)develop.

The thing is: excel fails to copy the data I want and paste it in the correct places in the 2nd worksheet. Sometimes it doesn't copy the data at all, and I get a blank sheet when I export it as pdf; sometimes it does copy everything, but paste the filled in template forms in the wrong cell.

The weird behavior: that only happens when Excel workbook is set to not visible. If I pause the execution and set it to visible, the routine runs just fine and I get a perfect pdf report at the end.

Any ideas of the reasons of this being happening and/or solutions to fix it?

Thanks in advance.

I thought it could be due to the VB6 exe doing things too fast for excel to process, so I added a couple of "Sleep 1000" in between lines of code hoping excel to be able to catch up. Didn't work.

    With xlsm.Worksheets("Template Cabos")
        .Activate
        Sleep 1000
        ActiveSheet.Range(Cells(1, 1), Cells(5, 36 + ONS)).Copy 'Copies the filled in form
        Sleep 1000
    End With

    With xlsm.Worksheets("Mem. Cabos")
        .Activate
        Cells(lin, 1).Select
        .Paste                         'Pastes it in the report workbook
        lin = lin + 6

        If (lin + 6) > (pag * 33) Then          'Checks if the next form would fit into the page    Checa se cabe outra ficha na página
            lin = pag * 33 + 1                  'If not, the marker "lin" goes to the top of page 2   Se não couber, o marcador "lin" vai pro topo da pág. 2
            .Range("A1:AJ5").Copy               'Copies the header of page 1                        Copia o cabeçalho da pág. 1
            Cells(lin, 1).Select
            .Paste                              'Pastes on the top of page 2                        Cola no topo da pág. 2
            pag = pag + 1                       'Acknowledges that now the report has one more page       Sinaliza que agora o relatório tem 2 páginas
            Cells(lin + 4, 35).Value = pag      'Types "2" in the field "page" of last page's header   Escreve "2" no campo "Pág." do cabeçalho da pág. 2
            lin = lin + 7                       'Moves the marker 7 line down to receive the next form    Move o marcador 7 linhas abaixo para receber a próx. ficha

        End If
    End With

In general, you want to [avoid using activate and select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Aug 29 '19 at 14:30
But without selecting a cell I'm not able to paste contents into it. I get an error with .Cells(lin,1).paste — Charley R., Aug 29 '19 at 14:35
I just realized you gave me a link to study in your comment. It seems very informative. I'm going to look it up and come back if I get any luck. Thanks. — Charley R., Aug 29 '19 at 14:41

score 1 · Answer 1 · answered Aug 29 '19 at 14:40

When copying and pasting in that manner it is possible for you to accidentally clear the clipboard while the program is running or maybe you manually (mouse click) change an active worksheet. This can lead to all types of issues when copying data from sheet to sheet.

A better approach is to simply reference the sheets directly. You don't have to worry about the correct sheet being active or the clipboard being manipulated in unexpected ways. Here's an example of how your first copy/paste would work.

Sheets("Mem. Cabos").Cells(lin, 1).Value = Sheets("Template Cabos").Range(Cells(1, 1), Cells(5, 36 + ONS)).Value

score 1 · Accepted Answer · answered Aug 29 '19 at 14:44

 With xlsm.Worksheets("Template Cabos")
    .Range(.Cells(1, 1), .Cells(5, 36 + ONS)).Copy _
           destination:=xlsm.Worksheets("Mem. Cabos"). Cells(lin, 1)
  End With    


With xlsm.Worksheets("Mem. Cabos")


    lin = lin + 6

    If (lin + 6) > (pag * 33) Then  
        lin = pag * 33 + 1                 
        .Range("A1:AJ5").Copy .Cells(lin, 1)
        pag = pag + 1                      
        Cells(lin + 4, 35).Value = pag    
        lin = lin + 7                       

    End If
End With

score 0 · Answer 3 · answered Aug 29 '19 at 15:11

After reading the link provided by @cybernetic.nomad, I tried:

    For 'paremeters

    'Code lines to fill in the template forms

    Dim tempRng As Range
    Dim repRng  As Range
    Dim xTemplate As Worksheet
    Dim xReport As Worksheet

    Set xTemplate = xlsm.Worksheets("Template Cabos")
    Set xReport = xlsm.Worksheets("Mem. Cabos")

    Set tempRng = xTemplate.Range(Cells(1, 1), Cells(5, 36 + ONS))
    Set repRng = xReport.Cells(lin, 1)

    tempRng.Copy repRng
    lin = lin + 6

    If (lin + 6) > (pag * 33) Then                                  'Checks if the next form would fit into the page    Checa se cabe outra ficha na página
        lin = pag * 33 + 1                                          'If not, the marker "lin" goes to the top of page 2   Se não couber, o marcador "lin" vai pro topo da pág. 2
        Set tempRng = xReport.Range("A1:AJ5")
        Set repRng = xReport.Cells(lin, 1)
        tempRng.Copy repRng                                         'Copies the header of page 1                        Copia o cabeçalho da pág. 1
                                                                    'Pastes on the top of page 2                        Cola no topo da pág. 2

        pag = pag + 1                       'Acknowledges that now the report has one more page       Sinaliza que agora o relatório tem 2 páginas
        xReport.Cells(lin + 4, 35).Value = pag      'Types "2" in the field "page" of last page's header   Escreve "2" no campo "Pág." do cabeçalho da pág. 2
        lin = lin + 7                       'Moves the marker 7 line down to receive the next form    Move o marcador 7 linhas abaixo para receber a próx. ficha

    End If


    PleaseWait.ProgressBar1.Value = PleaseWait.ProgressBar1.Value + Round((60 / xlsm.Worksheets("Cabos").Range("B40").Value), 0)

  Next

    Set tempRng = xTemplate.Range("A8:G13")             'Escreve a tabela de temperaturas admissíveis
    Set repRng = xReport.Cells(lin, 1)
    tempRng.Copy repRng

Got the error:

Run-Time error 1004: Method "Range" of object "Worksheet_" failed

Excel weird behavior when workbook set to not visible

3 Answers3