Help with code

[jcutler]

First problem.

Private Sub cmdbtnFilesave_Click()
If MsgBox("Would you like to save this file now?", Title:="Save File", Buttons:=vbCritical + vbYesNo) = vbYes Then
FileToSave = Application.GetSaveAsFilename("Tie Testing" & Format(Sheet1.Range("A1"), "mmmddyyyy" & "TO" & Format(Sheet5.Range("v1"), "mmmddyyyyy")), Filefilter:="Microsoft Office Excel Workbook(*.xlsx),*.xlsx", Title:="Save File")
If FileToSave <> False Then
ActiveWorkbook.SaveAs FileToSave, FileFormat:=xlNormal
End If
End If
End Sub

The above code is embedded into an activex command button. When I click on the command button in the cell, it displays the message box when I click yes, displays a "Run-time error '424': Object required."
The line "FileToSave = Application.GetSaveAsFilename("Testing" & Format(Sheet1.Range("A1"), "mmmddyyyy" & "TO" & Format(Sheet5.Range("v1"), "mmmddyyyyy")), Filefilter:="Microsoft Office Excel Workbook(*.xlsx),*.xlsx", Title:="Save File")" is highlighted in yellow. Did I code this wrong? Thanks.

[jcutler]

Figured out where I went wrong. This code goes from sheet1 to sheet5, I only had the default of sheet1-3 displayed, no sheet5. This brings up another issue. The date will always be in row A. Is there a way for excel to loop through the sheets and range(A:A) and find the last sheet used and the last cell within that range and how can I substitute "Format(Sheet5.Range("v1")..." with the last sheet used and the last cell with a date on that sheet. Hope that makes sense. Thanks.

[SamT]

How can we tell which one was the last sheet used?
Assume you mean Column "A"

The date will always be in row A
The last used cell in Column "A"
Dim LastCell As Range
Set LastCell = Cells(Rows.Count. "A").End(xlUp)

Filename
Dim FileDate As String
FileDate = Format(LastCell), "mmmddyyyy")
FileToSave = blah, blah, blah, & FileDate