VBA code runs correctly on debug (F8) but behaves differently (undisired way) when run through a button

Question

My code behaves similarly, runs ok when stepping trough(F8) but when assigning a macro to a picture, button or whatever, the code doesn't bahave as expected (also doesn't crash). Does anyone know a fix?

My code simply copies data from one sheet to another in a certain structure. Thank you.

Excel file on dropbox

Sub BaixarVenda()
pecas = 0
Sheets("Venda").Select
Range("A1").Select

If IsEmpty(Range("C7").Value) = False Then
'one item sale
Sheets("Venda").Range("C2").Copy Destination:=Sheets("Historico     vendas").Range("A1").End(xlDown).Offset(1, 0)
Sheets("Venda").Range("C4").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 1).PasteSpecial     Paste:=xlPasteValuesAndNumberFormats
Sheets("Venda").Range("C7").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 2)
Sheets("Venda").Range("C9").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 3).PasteSpecial  Paste:=xlPasteValues
Sheets("Venda").Range("C11").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 4)
Sheets("Venda").Range("C13").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
pecas = pecas + 1
End If

If IsEmpty(Range("E7").Value) = False Then
Sheets("Venda").Range("C2").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(1, 0)
Sheets("Venda").Range("C4").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial    Paste:=xlPasteValuesAndNumberFormats
Sheets("Venda").Range("E7").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 2)
Sheets("Venda").Range("E9").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 3).PasteSpecial Paste:=xlPasteValues
Sheets("Venda").Range("E11").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 4)
Sheets("Venda").Range("E13").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
pecas = pecas + 1
End If

If IsEmpty(Range("G7").Value) = False Then
'3 item sale

Sheets("Venda").Range("C2").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(1, 0)
Sheets("Venda").Range("C4").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Venda").Range("G7").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 2)
Sheets("Venda").Range("G9").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 3).PasteSpecial     Paste:=xlPasteValues
Sheets("Venda").Range("G11").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 4)
Sheets("Venda").Range("G13").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
pecas = pecas + 1
End If

If IsEmpty(Range("I7").Value) = False Then

Sheets("Venda").Range("C2").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(1, 0)
Sheets("Venda").Range("C4").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Venda").Range("I7").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 2)
Sheets("Venda").Range("I9").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 3).PasteSpecial     Paste:=xlPasteValues
Sheets("Venda").Range("I11").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 4)
Sheets("Venda").Range("I13").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
pecas = pecas + 1
End If

If IsEmpty(Range("K7").Value) = False Then
Sheets("Venda").Range("C2").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(1, 0)
Sheets("Venda").Range("C4").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("Venda").Range("K7").Copy Destination:=Sheets("Historico   vendas").Range("A1").End(xlDown).Offset(0, 2)
Sheets("Venda").Range("K9").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 3).PasteSpecial Paste:=xlPasteValues
Sheets("Venda").Range("K11").Copy Destination:=Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 4)
Sheets("Venda").Range("K13").Copy
Sheets("Historico vendas").Range("A1").End(xlDown).Offset(0, 5).PasteSpecial    Paste:=xlPasteValuesAndNumberFormats

pecas = pecas + 1
End If 
Sheets("Venda").Select
MsgBox "Venda finalizada"
End Sub

Please see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/q/10714251/11683). — GSerg, Feb 07 '17 at 23:46
It's hard to say why it doesn't *work as expected* when you've neither explained how you *expected* it to work or how it's *working instead* of as expected. Can you [edit] your question to actually *explain the problem you're having* with the code you posted? If you don't understand why you need to do so, call your auto repair shop and say *My car isn't working as expected. What's wrong and how much to fix it?* and see if they give you an answer without needing more information. — Ken White, Feb 07 '17 at 23:58
One POSSIBLE explanation is there is something to do with timing with your code. You are waiting in between actions longer than the CPU would perform the actions. This is not the ONLY option. — Ryan Wildry, Feb 08 '17 at 00:02

Michael · Accepted Answer · 2017-02-08T00:17:05.307

Are you sure the macro has been correctly assigned? Put a message box at the start of the macro to confirm it is running:

MsgBox "Running"

If the message box doesn't display when you click the button, then the macro isn't running at all. If the message box does display and you still don't have your data being copied correctly, then try the following:

Using Paste Special can have issues when the sheet you're pasting to isn't active. This issue can be hidden when you're stepping through code, especially if you're manually changing tabs to watch what happens at each step.

Note that you don't need to activate the sheet that you're copying from, so you just need to make the destination sheet active at the start of the macro instead:

Sheets("Historico vendas").activate

You just need to change your code so that everything that looks at your Venda sheet is qualified. It looks like all of your code is already qualified, except for the ranges in your If statements. Update those as well, and then see if it works. E.g.

If IsEmpty(Sheets("Venda").Range("C7").Value) = False Then

Thank you very much! Qualifying like your example solved my problem I was having! — Felipe Vitalino Mendonça, Feb 08 '17 at 23:31

VBA code runs correctly on debug (F8) but behaves differently (undisired way) when run through a button

1 Answers1