VBA: Run macro Yes or no prompt, Case without Select Case Error

[willow1985]

Hi, I have the following VBA code but it is giving me a Case without Select Case Error.

I am not sure what I am missing and hope you will be able to help. Here is the code:

Sub NewData()
'
' Paste Macro
'
If ThisWorkbook.Sheets("New Data").Range("A2").Value = "" Then
MsgBox "Work Order # has not been entered. Please enter a valid Work Order # to Proceed"
Exit Sub
End If
Dim cell As Range
    Dim ees As Range
    
'   Set range to Employee List
    Set ees = Sheets("Report").Range("A2:A1000000")
    
    For Each cell In Sheets("New Data").Range("A2:A50")
'       Check to see if value in cell
        If cell.Value <> "" Then
'           Check to see if value exists in other sheet
            If Application.WorksheetFunction.CountIf(ees, cell.Value) > 0 Then
                cell.Interior.Color = vbYellow
                MsgBox "Duplicate Work Order # Found, Cannot Update"
                Exit Sub
            End If
        End If
    Next cell



Dim Msg As String, Ans As Variant


    Msg = "Would you like to update the Warranty Report with this Data?"


    Ans = MsgBox(Msg, vbYesNo)


    Select Case Ans


        Case vbYes




    Sheets("New Data").Select
    Range("A2:L50").Select
        With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A2:L50").Select
    Selection.Copy
    
    Sheets("Report").Select
Range("W.Report").Cells(1, 1).End(xlDown).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
        Range("A1").Select
    Sheets("New Data").Select
    Range("A2:L50").Select
    Selection.ClearContents
    
    Sheets("Reference Data 1").Visible = True
    Sheets("Reference Data 2").Visible = True
    
    Sheets("Reference Data 1").Select
    ActiveSheet.PivotTables("WP.Table").PivotCache.Refresh


    Sheets("Reference Data 2").Select
    ActiveSheet.PivotTables("WT.Table").PivotCache.Refresh
    
    Sheets("Reference Data 1").Visible = False
    Sheets("Reference Data 2").Visible = False
    
    Sheets("Graphs").Select
    ActiveSheet.PivotTables("WYr.Table").PivotCache.Refresh
    ActiveSheet.ChartObjects("W.Yr").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
    ActiveSheet.ChartObjects("WP.Chart").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
    ActiveSheet.ChartObjects("WT.Chart").Activate
    ActiveChart.PivotLayout.PivotTable.PivotCache.Refresh
    Sheets("New Data").Select
        Range("A2:L50").Select
        With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    Range("A2").Select
    MsgBox "Update Complete"


        Case vbNo
        GoTo Quit:
    End Select


Quit:


End Sub

Thank you

Carla

 

[Fluff]

You're missing an End With

Sheets("New Data").Select
        Range("A2:L50").Select
        With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
[COLOR=#ff0000]       End With[/COLOR]
    Range("A2").Select
    MsgBox "Update Complete"


        Case vbNo
        GoTo Quit:
    End Select

 

[willow1985]

Thank you so much!

Is there also a vba code to clear the conditional formatting In Sheets("New Data").Range("A2:L50")?

Currently in the code I just clear the color format.

Thank you

Carla

 

[Fluff]

Try

With Sheets("New Data").Range("A2:L50")
   .FormatConditions.Delete
   With .Interior
      .Pattern = xlNone
      .TintAndShade = 0
      .PatternTintAndShade = 0
   End With
End With
Range("A2").Select
MsgBox "Update Complete"