Macro save as Expansion

[Guest]

Further to my save as Macro (thanks for the help on that by the way)
I'm just looking to write an If at the end which if true will also save the
file to another location, here is what ive put,

******************************************
Sub NewSave()
Dim sStr As String
sStr = "\\stw.intra\STW\Financial Services\FS\·Section-" & _
"Wide Data\AMS\PA Journals\Personal\PA-"


ActiveWorkbook.SaveAs _
Filename:=sStr & Cells(6, 16) & _
Format(Now(), "-yyyy.mm.dd") & ".xls"

If Range(D1) = "UPLOAD" Then

Run.UploadSave

End If
End Sub
***************************************
Sub UploadSave()

Dim sStr As String
sStr = "\\stw.intra\STW\Financial Services\FS\·Section-" & _
"Wide Data\AMS\PA Journals\Journals to be Uploaded\PA-"


ActiveWorkbook.SaveAs _
Filename:=sStr & Cells(6, 16) & _
Format(Now(), "-yyyy.mm.dd") & ".xls"

End Sub
**********************************************

but it doesn't seem to like my IF Statement,

Any suggestions?

thanks,

Andy

 

[Bernie Deitrick]

Andy,

Here's how:

Sub NewSave2()
Dim sStr As String
sStr = "\\stw.intra\STW\Financial Services\FS\·Section-" & _
"Wide Data\AMS\PA Journals\Personal\PA-"

ActiveWorkbook.SaveAs _
Filename:=sStr & Cells(6, 16) & _
Format(Now(), "-yyyy.mm.dd") & ".xls"

If Range("D1").Value = "UPLOAD" Then
UploadSave2
End If

End Sub

Sub UploadSave2()

Dim sStr As String
sStr = "\\stw.intra\STW\Financial Services\FS\·Section-" & _
"Wide Data\AMS\PA Journals\Journals to be Uploaded\PA-"

ActiveWorkbook.SaveCopyAs _
Filename:=sStr & Cells(6, 16) & _
Format(Now(), "-yyyy.mm.dd") & ".xls"

End Sub


Though I would suggest changing your date formatting to yyyy-mm-dd so that
you don't have extra . in the file name.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks, will have a look at that,

But not sure why you say to change the . in the date format, why would this
benefit me?

Thanks

Andy

 

[Bernie Deitrick]

Andy,

File names like myfile.2005.JAN.01.xls may be confusing to other people if
they are using different display options in Explorer, that's all...

HTH,
Bernie
MS Excel MVP