Object Variable or with block variable not set.

I have an error that I just can't figure.

Option Explicit
Public Sub CommandButton1_Click()
    Dim CurrentDate As String
    Dim PriorDate As String
    Dim WrkbookPrior As Workbook
    Dim WrkbookCurrent As Workbook
    Dim WrkbookCompare As Workbook
    Dim FilePath1 As String
    Dim FilePath2 As String
    Dim FileNamePrefix1 As String
    Dim FileNamePrefix2 As String
    
    FilePath1 = "XXX:\Users\YYYYY1\Desktop\"
    
    PriorDate = Format(TextBoxPriorDate.Text, " mm-dd-yy")
    CurrentDate = Format(TextBoxCurrentDate.Text, " mm-dd-yy")
    
    FileNamePrefix1 = "Option EPMS Valuation"
    FileNamePrefix2 = "Option EPMS Valuation Tool"
      
    WrkbookPrior = FilePath1 & FileNamePrefix1 & PriorDate & ".xlsx" <--------- Error
    WrkbookCurrent = FilePath1 & FileNamePrefix1 & CurrentDate & ".xlsx"
    WrkbookCompare = FilePath1 & FileNamePrefix2 & ".xlsx"
    
    Workbooks.Open WrkbookPrior
        ThisWorkbook.Sheets.Copy After:=Workbooks("WrkbookCompare.xlsx").Worksheets(Workbooks("WrkbookCompare.xls").Worksheets.Count)
    Workbooks.Open WrkbookCurrent
        ThisWorkbook.Sheets.Copy After:=Workbooks("WrkbookCompare.xlsx").Worksheets(Workbooks("WrkbookCompare.xls").Worksheets.Count)

End Sub

This error is occuring because you are trying to set a workbook type variable to a string value. Try this instead:

Set WrkbookPrior = Workbooks(FilePath1 & FileNamePrefix1 & PriorDate & ".xlsx").Open

Instead of

WrkbookPrior = FilePath1 & FileNamePrefix1 & PriorDate & ".xlsx"
&
Workbooks.Open WrkbookPrior

Sounds like the file is not there. Make sure you have all your slashes.

Look at what you have put together in your code. Right now it is:

XXX:\Users\YYYYY1\Desktop\Option EPMS Valuation 03-31-11.xls.

You need to change the month to a single character to get rid of the zero in front of the 3.

Use the immediate window to check the file path and name and compare it to the filepath and name in the windows explorer.

Do this:

? FilePath1 & FileNamePrefix1 & PriorDate & ".xlsx"

Like this instead:

Set WrkbookPrior = Workbooks(FilePath1 & FileNamePrefix1 & " " & PriorDate & ".xlsx").Open