VBA Macro Help

[juan4412]

I have a date field in a spreadsheet, and how can I say make the date be date()+1 save then once it save change back to date()?

 

[VoG]

Try like this

With Range("A1")
    .Value = .Value + 1
    ActiveWorkbook.Save
    .Value = .Value - 1
End With

 

[juan4412]

I want to add in these lines of code but they are bringing in some pretty wierdy compile errors.

With Range("I4")
    .Value = .Value + 1
    .SaveAs Filename:="C:\Jones\Reports\Daily & ActiveWorkbook.Name & "_" & Format(Date, "mmddyyyy") & ".xls"
    Run "PrintToPDF_MultiSheetToOne_Early"
    .Value = .Value - 1
    End With

And it tells me Object doesn't support this property or method???
on the .SaveAs line

 

[VoG]

Try

With Range("I4")
    .Value = .Value + 1
    ActiveWorkbook.SaveAs Filename:="C:\Jones\Reports\Daily" & ActiveWorkbook.Name & "_" & Format(Date, "mmddyyyy") & ".xls"
    Run "PrintToPDF_MultiSheetToOne_Early"
    .Value = .Value - 1
End With

 

[juan4412]

ADVERTISEMENT

That code runs through the motions but doesn't actually modify the date. I just double checked and it isn't date() but it's Today() that is in the cell. Does that make a difference as for the VBA that should be used to change it?

 

[VoG]

Maybe like this

With Range("I4")
    .Formula = "=TODAY() + 1"
    ActiveWorkbook.SaveAs Filename:="C:\Jones\Reports\Daily" & ActiveWorkbook.Name & "_" & Format(Date, "mmddyyyy") & ".xls"
    Run "PrintToPDF_MultiSheetToOne_Early"
    .Formula = "=TODAY()"
End With

 

[juan4412]

ADVERTISEMENT

Same error as before with that code. Object doesn't support this property or method???
on the .SaveAs line

 

[VoG]

It has to be Activeworkbook.saveas not .saveas

 

[juan4412]

That got it. Thank you!