The Print dialogue interferes with the following Macro code

[Jongering]

Hi all,

Another question here. I have a MACRO that combines a "save as" command with a command to export specific data to another sheet. However, the "save as" dialogue box seems to interfere with the rest of the MACRO. Excel tries to continue when it should basically WAIT untill the saving has been done.

How should I alter my code so that it doesn't crash anymore?

Hope you can help. Thanks in advance!

Sub EXPORT_Factuur_naar_admin_en_SAVE()

'Show Message box'

Dim nResult As Long
    nResult = MsgBox( _
    Prompt:="Zijn alle posten naar behoren ingevoerd en sluit de financiele data aan bij de organisatiegegevens? Zo ja, dan wordt dit overzicht geprint en wordt de hoofdgegevens van deze factuur digitaal opgeslagen. ", _
    Buttons:=vbYesNo)
    If nResult = vbNo Then
    Exit Sub
    End If
    
'Save the file'
   Application.Dialogs(xlDialogSaveAs).Show

'THIS IS WHERE THE PROBLEM OCCURS'

'select data from this sheet and paste in the next sheet'
Range("J20:J27").Select
    Application.CutCopyMode = False
    Selection.Copy
Sheets("Betalingsadmin.").Select
Range("B1000").End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

 
 'select proper sheet'
 Sheets("Factuur").Select

End Sub

[puru.sve]

Try this and let me know ...

 
Sub EXPORT_Factuur_naar_admin_en_SAVE()

'Show Message box'

    Dim nResult As Long
    
    nResult = MsgBox( _
    Prompt:="Zijn alle posten naar behoren ingevoerd en sluit de financiele data aan bij de organisatiegegevens? Zo ja, dan wordt dit overzicht geprint en wordt de hoofdgegevens van deze factuur digitaal opgeslagen. ", _
    Buttons:=vbYesNo)
    
    If nResult = vbNo Then
        Exit Sub
    End If
    
    'Save the file'
   Application.Dialogs(xlDialogSaveAs).Show

    'THIS IS WHERE THE PROBLEM OCCURS'

    'select data from this sheet and paste in the next sheet'
    Range("J20:J27").Copy
    
    Sheets("sheet2").Range("B" & Sheets("sheet2").Range("B1000").End(xlUp).Row + 1).Offset(0, -1).PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

    Application.CutCopyMode = False
    'select proper sheet'
    Sheets("Factuur").Select

End Sub

[Jongering]

Thanks! It got the job done!