VBA to save file as to a directory

[sandra40s]

I need help in creating a VBA to save file as to a directory. I have the old file names including the directory in Worksheet1. I have the new file names including the new directory in Worksheet2. There are over 200 that needs to be saved as with the new name and directory. Please help.

 

[sandra40s]

does anyone know of an easier VBA to save a file "save as" with different path and new file name?

 

[Useful]

does anyone know of an easier VBA to save a file "save as" with different path and new file name?

it works fine. For better understanding it is recommend to put old full file name starting from A1 and new full file name starting from B1 (both A1 and B1 are in the one sheet)
how it works:

it will save A1 full file name as B1, A2 as B2, A3 as B3 and so on...

(here .Cells(i, 1) your old name and .Cells(i, 2) as new name)

Sub Savewb()
With ActiveSheet
lst = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To lst
FileCopy .Cells(i, 1), .Cells(i, 2)
Next
End With
End Sub

 

[sandra40s]

I tried the last code and I still got the message that path not found. I did a record macro to verify the path and it is identical to what I was using. Below is the recorded macro for one of them. I changed the name to something else in order to display the code.

ChDir "F:\AAAA\BBBBB\CCCCC\2015 Color Files"
Workbooks.Open Filename:= _
"F:\AAAA\BBBBB\CCCCC\2015 Color Files\XXX 2015 Green.xlsm"
ChDir _
"F:\AAAAA\PPPPPP\Horse\Purple\Green\Green 2015"
ActiveWorkbook.SaveAs Filename:= _
"F:\AAAAA\PPPPPP\Horse\Purple\Green\Green 2015\Horse 2015 Green.xlsm"_
, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close

 

[Fluff]

Hello sandra40s
Both sets of code from Useful work perfectly & are about as easy as it gets for copying multiple files.
With your above example you should have
in A1
F:\AAAA\BBBBB\CCCCC\2015 Color Files\XXX 2015 Green.xlsm
& in B1
F:\AAAAA\PPPPPP\Horse\Purple\Green\Green 2015\Horse 2015 Green.xlsm
Is this the case?

 

[sandra40s]

That is exactly what I have. Old file name in A1 and new file name in B1 and the last code you gave me. I started with a new spreadsheet and got a highlighted line item on FileCopy .Cells(i, 1), .Cells(i, 2). Run-time error "76": Path not found. I know the paths are there so I am thinking that I may be missing something simple like a symbol or mark before or after the file name. Just a thought.

 

[Fluff]

so I am thinking that I may be missing something simple like a symbol or mark before or after the file name.

A space would be the most probable as it's not very obvious.
Something to try is

Sub test()
Workbooks.Open (Range("A1"))
End Sub

If the file opens then the existing path/filename is ok. In which case copy the 1st file to the new location & change it's name to the new version. Then change A1 to B2 & run this code again

 

[sandra40s]

The old file name opened. No problem. I saved the file with the new name in the new path. Ran the open code again just to see if it would open. I got the message “Couldn’t find”. It is possible it was moved, renamed or deleted? The file is there because I manually saved it with the new name.

 

[sandra40s]

I did place the new file path and name in cell A1 before I ran the open code. I forgot to mention that in my previous message.

 

[sandra40s]

Note: The new file name does not exist in the new folder. I am trying to save the old file name to the new path with a new name.

 

[Fluff]

Hia
All I can suggest is to run Useful's code in stages to try & find where the path goes wrong.
So change the values in column B to read

F:\AAAAA\Horse 2015 Green.xlsm

& if that works then change to

F:\AAAAA\PPPPPP\Horse 2015 Green.xlsm

etc