Macro to Save As to a specific location

C

Char4500

I have a Macro that I want to save the file using the date that's listed in a
specific cell, however I want the file to be placed in a specific folder.
Below is the code I'm using, however It does not saving it in
"T:\MBRProjects\Reports\Weekly Hour Forms\" it saves the file to the last
folder I was in. How do I correct this?

Sub FridayUploadPrep()
'
' FridayUploadPrep Macro
' Macro recorded 9/28/2006 by Charlene Parker
'

'
Sheets("Friday").Select

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim strFileName As String
Dim strPath As String

Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Friday")
varVal = ws.Range("N1").Value
strPath = "T:\MBRProjects\Reports\Weekly Hour Forms\"

If IsDate(varVal) Then
strFileName = "NPBR " & Format(CStr(varVal), "mm-dd-yyyy") & ".xls"
Else
strFileName = "NPBR " & Format(CStr(Date), "mm-dd-yyyy") & "saved.xls"
End If

ActiveWorkbook.SaveAs Filename:=strPahtName & strFileName

Set wb = Nothing
Set ws = Nothing

End Sub
 
D

Dave Peterson

strPahtName
is a typo.

Check your spelling of your variables.

If you're lazy (like me), you'll want to add:

Option Explicit
to the top of the module. Then VBA won't even run your code as long as there's
an error like this.
 
C

Charles Chickering

Charlene, from what I can tell you have a typo. strPath does not =
strPathName. Just a note, to prevent some errors like this, you could require
variable declaration. which is found under Tools... Options... Editor Tab
"Require Variable Declaration". This will place the statement "Option
Explicit" at the top of every new module, which will generate a variable not
defined error when ever your code is compiled and you don't have all your
variables named the same.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top