Need a Macro to save a file

[Dean Staples]

Hey All,

I am a novice to Macro's so please explain things as if i am 2 years old not 46

I need to create a Macro that allows the User to manually select a workbook from the network.

the Macro then needs to save that workbook with a different name in a different directory.

I have written this but have been having trouble with runtime error 1004 "excel cannont access file "trying to save a copy of the workbook


If anyone could either suggest a solution to my problem or provide alternate code to do the same thing, It would be greatly appriecated -have spent 5 hours or more trolling excel boards looking for an answer

thanks
Dean

Dim fName As Variant
Dim savefilename As Variant
MsgBox "Please Select MicroJets New Price Book"
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened")
If fName = False Then Exit Sub
Set bk1 = Workbooks.Open(fName)
savefilename = Mid(fName, InStrRev(fName, "\") + 1)
MsgBox savefilename

Dim MyDate
MyDate = Date    ' MyDate contains the current system date.
Dim MyTime
MyTime = Time    ' Return current system time.
Dim TestStr As String
TestStr = Format(MyTime, "hh.mm.ss")
Dim Test1Str As String
Test1Str = Format(MyDate, "DD-MM-YYYY")

Application.DisplayAlerts = False

ActiveWorkbook.SaveCopyAs filename:="C:\Users\Public\Public Documents\Work-Macros\" & Test1Str & " " & TestStr & " " & savefilename

Application.DisplayAlerts = True

[millz]

assuming your getting of file works,

Dim fName As Variant
Dim savefilename As Variant
MsgBox "Please Select MicroJets New Price Book"
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", Title:="Select File To Be Opened")
If fName = False Then Exit Sub
savefilename = Mid(fName, InStrRev(fName, "\") + 1)

Dim MyDate
MyDate = Date    ' MyDate contains the current system date.
Dim MyTime
MyTime = Time    ' Return current system time.
Dim TestStr As String
TestStr = Format(MyTime, "hh.mm.ss")
Dim Test1Str As String
Test1Str = Format(MyDate, "DD-MM-YYYY")

FileCopy(fName, "C:\Users\Public\Public Documents\Work-Macros\" & Test1Str & " " & TestStr & " " & savefilename)

[Dean Staples]

Hey Millz,

that gives me a compile error - syntax error

[millz]

At which line did the error occur?

[millz]

Sorry, realised the problem. Remove parenthesis for FileCopy:

FileCopy fName, "C:\Users\Public\Public Documents\Work-Macros\" & Test1Str & " " & TestStr & " " & savefilename

[Dean Staples]

That worked -I just had to switch the file access to stop the syntax