Excel Macro doesn't work when called from Function

Question

I got a Macro that works perfectly, when I call it from a button or with F5/ F8. But when I try to start it from a Function (HALB_auto_erstellen) it stops without an error or just doesn't do the steps. Does someone know, what causes this weird behaviour?

Sub HALBneu()

answer = MsgBox("Soll ein neuer HALB Artikelkopf mit ??? erstellt werden?", vbQuestion + vbYesNo + vbDefaultButton2, "Neuer Artikel")
If answer = vbYes Then
    Sheets("BOM_Kopf").Visible = True
    Worksheets("HALB").Activate
    m = Worksheets("HALB").Cells(Rows.Count, 1).End(xlUp).Row
    m = m + 16
    
    Sheets("BOM_Kopf").Activate
    If Worksheets("BOM").Cells(6, 1) <> "leerer Artikelnummer" Then
        Worksheets("BOM_Kopf").Cells(23, 10) = Worksheets("BOM").Cells(6, 1)
    End If
        Worksheets("BOM_Kopf").Range("A21:M28").Copy
    Worksheets("HALB").Activate
    Worksheets("HALB").Cells(m, 1).PasteSpecial Paste:=xlPasteFormulas, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False
    
    Worksheets("BOM_Kopf").Range("A21:M28").Copy
    Worksheets("HALB").Cells(m, 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Sheets("BOM_Kopf").Visible = False

End If
        
End Sub



Function HALB_auto_erstellen()
    Call HALBneu
End Function

yes, it does. When I run through the code it runs until the last line. But it doesn't do the Copy&Paste and also doesn't activate any worksheets. — Fabian, Nov 04 '21 at 10:43
You seem to be pasting to the same row of `HALB` each time, do you need to recalculate `m` each time you've pasted? — Nathan_Sav, Nov 04 '21 at 10:46
The first paste is for the numbers and formulas. The second time is to paste the format. That's why I paste xlPasteFormulas the first time and xlPasteFormats the second time. — Fabian, Nov 04 '21 at 10:49
Are you, in turn, calling `HALB_auto_erstellen` from a sheet? — GSerg, Nov 04 '21 at 11:08
@GSerg yes, I call it with an if function in the Worksheet "BOM" — Fabian, Nov 04 '21 at 11:42

Excel Macro doesn't work when called from Function

0 Answers0