Macro save as Expansion

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

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

"Momo" <[email protected]> wrote in message
news:[email protected]...
> 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

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" wrote:

> 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
>
> "Momo" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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

"Momo" <[email protected]> wrote in message
news:[email protected]...
> 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