save spreadsheet with a filename that contains a reference to the last cell in a column

[stjonca]

I’m sure that it’s here somewhere but my apologies. I haven’t been able to complete a search to answer my question.

I’m trying to append a line of code to an existing VBA module that will save the spreadsheet with a filename that contains a reference to the last cell in a column (B) that contains a date. Also the filename should contain the date and time that the file was saved.

e.g. C:\Users\Sam\OneDrive\Statement for 30-Jun-16 and file was saved on 12_11_2016 09 15 PM.xlsm

With the code below I get “True” where I would like the contents of the last cell in column B which contains a date so the result currently looks like this:

C:\Users\Sam\OneDrive\Statement for True and file was saved on 12_11_2016 09 15 PM.xlsm

The format of the date is not important but if it is different from the format of the cell, can it be changed?

The answer is no doubt a simple fix but it is eluding me. Any advice is appreciated.

  ActiveWorkbook.SaveAs Filename:="C:\Users\Sam\OneDrive\Statement for " _
    & Range("B6").End(xlDown).Select & " and File was Saved on " & _
    Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

 

[myall_blues]

Hi

Maybe something like:

Dim LastCell as String
LastCell = Range("B6").End(xlDown).Value
ActiveWorkbook.SaveAs Filename:="C:\Users\Sam\OneDrive\Statement for " _
    & LastCell & " and File was Saved on " & _
    Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

You can probably do without the LastCell = Range("B6").End(xlDown).Value line and just change the .Select in your original to .Value, but I'm just showing for purpose of clarity. You might need to format the output to make it look how you want.

Regards

Murray

 

[stjonca]

Thanks for the response Murray.

Unfortunately I’m still doing something wrong it seems.

I pasted your code into the worksheet module and on running it received the “Runtime Error 1004” and I haven’t been able to sort that out.

I feel that I might be missing something in your response so just to make sure that something else wasn’t getting in the way I created a new clean worksheet that contained only consecutive dates in column B from B6 to B13. I added a module and pasted in your suggested and got the same result and the same error.

One worksheet with one module with this in it:

Sub SaveFileWithDates()
Dim LastCell As String
LastCell = Range("B6").End(xlDown).Value
ActiveWorkbook.SaveAs Filename:="C:\Users\Fox\OneDrive\xxxSave the File " _
    & LastCell & " and File was Saved on " & _
    Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

You mentioned changing my original code from .Select to .Value. That was actually the first fix that I tried. That too resulted in the “Runtime Error 1004”. I felt that I was getting close to discovering the solution when I managed to save the file albeit with True when I wanted the cell contents date but I just can’t land on the fix.

The fact that “Range("B6").End(xlDown).Select” resolves into “True” in the filename output seems to be the big clue but I am far past my knowledge limits on this one.

 

[myall_blues]

Can you tell me the result if you just run this?

Sub test()
Dim LastCell as String
LastCell=Range("B6").End(xldown).value
msgbox(LastCell)
End sub

Regards

Murray

 

[stjonca]

The message box returns with the contents of B13, the last date in the column.

 

[stjonca]

It is curious to me that your test code works as expected but when I add the SaveAsFilename to it I still get the “Runtime Error 1004”.

I just can’t see the problem. By the way I am using Excel 2007 in case that makes any difference.

Option Explicit
Sub test()
Dim LastCell As String
LastCell = Range("B6").End(xlDown).Value
MsgBox (LastCell)
 
ActiveWorkbook.SaveAs Filename:="C:\Users\Fox\OneDrive\Statement for " _
    & LastCell & " and File was Saved on " & _
    Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
    WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
 
End Sub

However by just changing the .Value to .Select the code completes after the message box but as before I get the “True” in the filename instead of the date.

Thanks

 

[myall_blues]

This code works for me without error. Obviously you would need to change the pathname part from
C:\Users\tylermh\desktop\Statement to
C:\Users\Fox\OneDrive\Statement to suit.

Option Explicit


Sub test()
Dim LastCell As String
LastCell = Range("B6").End(xlDown).Value
MsgBox (LastCell)

ActiveWorkbook.SaveAs Filename:="C:\Users\tylermh\desktop\Statement for " _
& Format(LastCell, "mm-dd-yyyy") & " and File was Saved on " & _
Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

End Sub

Regards

Murray

 

[stjonca]

Thanks Murray;

The last code that you offered worked exactly like I had hoped for. I was curious as to why that when I was testing code I wasn’t having any success. So I compared the first suggestion that you provided with the last.

I discovered two differences. One was the (SaveAs Filename:="C:\) had a space in it that the compiler (sometimes) didn’t like. The code that I offered had that issue as well but the curious thing to me is that it would run when .Select was used and not when .Value was included.

The other was that you had added the date formatting (Format(LastCell, "mm-dd-yyyy"). Wondering if that would make the difference I pasted that into the original code thinking that other than the formatting that they would be equal.

I noticed that, the MessageBox now displays “True” instead of the date. I have to park this and head out on a mission without hurting my brain any longer to try to understand all of this but it is working and I want to thank you for your help. Much appreciated.

 

[myall_blues]

You're welcome. My assumption was that the default slashes "/" in the date were causing the problem, which appear in my date formats and I assume do so in yours.

You can remove the Msgbox now of course - it just served for testing purposes.

Regards

Murray

 

[stjonca]

Back again…

Although the solution worked, the date appears as Dec-29-1899 in the filename regardless of what I select from the calendar form. I thought that I would be able to sort this out myself, but after a lot of failed attempts it seems that I need to reach out again.

As mentioned above, the dates in Column B are from a form with a calendar control.

From research I concluded that I should be using CDate when writing back to the cell however where I am using it doesn’t yield the required change in the date.

Here is the actual code from the UserForm but pared down it to only the relevant items (I hope).

Advice much appreciated.

Public Sub Add_Click()
 
wsDATABASE.Select
 
    Range("B6").End(xlDown).Offset(1, 0).Select
    ActiveCell.Offset(0, -1).Value = WorksheetFunction.Max(Columns("A")) + 1   ‘to add a consecutive ID #
    ActiveCell.Offset(0, 0).Value =[B] [COLOR=#ff0000]CDate[/COLOR][/B](DateTxt.Value)   ‘adds date from the calendar to the worksheet
   
    Dim LastCell As String
    LastCell = Range("B6").End(xlDown).Select
    ActiveWorkbook.SaveAs Filename:="C:\Users\Fox\OneDrive\Test3\TEST 3 " _
        & Format(LastCell, "mmm-dd-yyyy") & " and File was Saved on " & _
        Format(Now(), "mm_dd_yyyy hh mm AMPM"), _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, Password:="", _
        WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    Unload Me
End Sub

Private Sub Cancel_Click()   ‘unloads the UserForm
    Unload Me
End Sub
Private Sub DateTxt_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm1.Show 'calendar
End Sub

Private Sub UserForm_Initialize()
    DateTxt.Text = Format(Date, "dd/mmm/yyyy")
End Sub

Here is the code from UserForm1 (the calendar form).

Option Explicit
 
Private Sub Calendar1_Click()
    EnterScoresForm.DateTxt.Value = [B][COLOR=#ff0000]CDate[/COLOR][/B](Calendar1.Value)
    Unload Me
End Sub
 
Private Sub UserForm_Activate()
    Me.Calendar1.Value = Date
End Sub