Adding a prompt box before executing a macro

[Harry_T]

Hi everyone,

I have a macro that I know works that would copy data from a range on one sheet within the workbook and paste the data as values in the desired location.

Macro:
Sheets("Master_Listing").Select
Range("B2:B349").Select
Selection.Copy
Sheets("Busy Season Tracker").Select
Range("B3:b350").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

I tried to look up VBA on the forum and tried two different VBA strings but seem to keep getting a debug error with both strings of VBA code. When hitting debug, it takes me to the VBA code that is referencing the range that the macro is trying to copy the data from...Range("B2:B349").Select from worksheet "Master_Listing"

#1:

Dim Msg As String, Ans As Variant


Msg = "Would you like to update the listing?"


Ans = MsgBox(Msg, vbYesNo)


Select Case Ans


Case vbYes

**** The VBA from the Macro ****

Case vbNo
GoTo Quit:
End Select


Quit:

#2



Dim reply
reply = MsgBox("Do you want to proceed.?", vbYesNo)
If reply = vbNo Then Exit Sub

**** The VBA from the Macro ****


Is there something that I'm doing wrong here? Thanks for your help in advance!

 

[Trebor76]

Hi Harry_T,

Try this:

Option Explicit
Sub Macro3()
    
    If MsgBox("Would you like to update the listing?", vbQuestion + vbYesNo) = vbYes Then
        Application.ScreenUpdating = False
        Sheets("Master_Listing").Range("B2:B349").Copy
        Sheets("Busy Season Tracker").Range("B3:B350").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        With Application
            .CutCopyMode = False
            .ScreenUpdating = True
        End With
    End If
 
End Sub

Regards,

Robert

 

[Harry_T]

Thank you Trebor. That worked for me.

 

[Trebor76]

You're welcome and thanks for like