Help with code

pooky · March 10th, 2014, 06:22 PM

First problem.

Code:

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.

pooky · March 10th, 2014, 06:40 PM

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.