Update Now()

[donh]

Hi,

I have used some VBA from another posting which allows cell values to
be incorporated into a file name. I have added Now() onto my
worksheet and added an if statement that returns Now() date if a
refrence number is not present.

I have also created a very crude way of updating Now() when saving.

As you may of seen from other posts I am very new to VBA and although
this works, I would really like to how it could of best been done.


Sub Save()

Application.ScreenUpdating = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=False,
Scenarios:=False

Range("E12").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

With ActiveWorkbook
.SaveAs Filename:="D:\Test\" _
& .Worksheets("sheet1").Range("B12").Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With



End Sub


Many thanks

DonH

 

[Bob Phillips]

With ActiveWorkbook
.SaveAs Filename:="D:\Test\" _
& format(date,"yyyy_mm_dd") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[donh]

Thanks Bob, but this takes the the if statement out of the loop (I
think) I am wanting the date to kick in as alternative file name if a
reference number isn't present in a cell address, ie Cell A1 contains
Visit No1234

Many thanks

DonH

 

[Don Guillett]

something like this??

Bob Phillips wrote:

With ActiveWorkbook
if .range("a1")="whatever" then
.SaveAs Filename:="D:\Test\" _
& format(date,"yyyy_mm_dd") & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
else
??
end if
End With

 

[donh]

Thanks for that, sounds like good advice. The trouble is when you are
starting out none of it makes sense and it takes an age to understand
HOW and WHY things are constructed as they are.

Please don't get me wrong this group is a fantastic resource (and many
thanks to those who have taken time to reply) but sometimes the
snippets of advice can leave me even more confused. It can be a
difficult place for a beginner, wanting to know HOW and WHY.

Thanks again to all.

Merry Christmas

DonH

 

[Bob Phillips]

Also it is difficult to gauge whether the OP just wants a solution, or wants
to understand. Because of this I just tend to give (what I think is) the
solution, and expand upon it if asked. If you want more up-front, just say
so <g>

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Bob Phillips]

You would save it with the contents of A1?

With Activesheet
If .Range("A1").Value <> "" Then
.SaveAs Filename:="D:\Test\" _
& .Range("A1").Value & ".xls", _
FileFormat:=xlNormal
Else
.SaveAs Filename:="D:\Test\" _
& Format(date,"yyyy_mm_dd") & ".xls", _
FileFormat:=xlNormal
End With

Note that I have dumped all of the non-used SaveAs arguments.



--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Dave Peterson]

Just a typo alert:

With ActiveSheet
If .Range("A1").Value <> "" Then
.Parent.SaveAs Filename:="D:\Test\" _
& .Range("A1").Value & ".xls", _
FileFormat:=xlNormal
Else
.Parent.SaveAs Filename:="D:\Test\" _
& Format(Date, "yyyy_mm_dd") & ".xls", _
FileFormat:=xlNormal
End If
End With

Missing an "end if" and a couple of parents--just like "Home Alone"????

http://imdb.com/title/tt0099785/

 

[donh]

Thanks Dave that worked as I needed.

I think thats enough from me.

Happy Christmas to all

DonH

 

[Dave Peterson]

Nicely snipped!

Thanks Dave that worked as I needed.

I think thats enough from me.

Happy Christmas to all

DonH