macro not running correctly

[steve400243]

Hello, This macro "getuniques" runs a new report if a user selects yes in the message box when accessing the file. If the report is opened and a new one ran and it is on Sheet1, it gets a run-time error 1004, Application defined or subject defined error, and the "Sheets("ANALYSIS") row of code Highlights? If the user is on the ANALYSIS sheet and selects YES, it works fine? Anyone know why this would happen? And what can correct this?

Private Sub Workbook_Open()
    If MsgBox("Hello, Would you like to Create a new report?", vbYesNo, "CFS DATA ANALYSIS REPORT") = vbYes Then
        Call t
        Call getuniques
    End If
End Sub

Sub getuniques()
   Dim d As Object, c As Variant, i As Long, lr As Long
   Set d = CreateObject("Scripting.Dictionary")
   lr = Cells(Rows.Count, 1).End(xlUp).Row
   c = Sheets("Sheet1").Range("A2:A1000" & lr).Value2
   [COLOR=rgb(184, 49, 47)]Sheets("ANALYSIS").Range("U3", Range("U" & Rows.Count).End(xlUp)).ClearContents[/COLOR]
   For i = 1 To UBound(c, 1)
     If c(i, 1) <> "" Then d(c(i, 1)) = Empty
   Next i
   Sheets("ANALYSIS").Range("U3").Resize(d.Count) = Application.Transpose(d.Keys)
End Sub

 

[jasonb75]

The second instance of Range is not qualified with a sheet name, so it defaults to the active sheet (sheet1), this should fix it.

Sheets("ANALYSIS").Range("U3", Sheets("ANALYSIS").Range("U" & Rows.Count).End(xlUp)).ClearContents

 

[steve400243]

The second instance of Range is not qualified with a sheet name, so it defaults to the active sheet (sheet1), this should fix it.

Sheets("ANALYSIS").Range("U3", Sheets("ANALYSIS").Range("U" & Rows.Count).End(xlUp)).ClearContents

Excellent, Thanks for your help!