I'd like to copy an existing workbook (that's closed, preferably) and name
the copy based on cell values. So, for example in column A of the active
workbook I have a list of different excel workbooks that are closed (with the
file path), and in column B I have a list of names that I want to call the
copied workbooks. For all of the items in the list I'd like to copy the
respective workbook and give it the listed name. I figure I need a VBA loop,
but have no idea on the commands needed. Thanks.

Sub Copyfiles()

Set fscopy = CreateObject("Scripting.FileSystemObject")
RowCount = 1
Do While Range("A" & RowCount) < ""
OldName = Range("A" & RowCount)
NewName = Range("B" & RowCount)
fscopy.CopyFile OldName, NewName

RowCount = RowCount + 1
Loop

End Sub


"ajd" wrote:

I'd like to copy an existing workbook (that's closed, preferably) and name
the copy based on cell values. So, for example in column A of the active
workbook I have a list of different excel workbooks that are closed (with the
file path), and in column B I have a list of names that I want to call the
copied workbooks. For all of the items in the list I'd like to copy the
respective workbook and give it the listed name. I figure I need a VBA loop,
but have no idea on the commands needed. Thanks.

Thanks. I can't get the macro to work though because it has a "file not
found" error. What format does the link to the old file and the name of the
new file need to be in?

Right now within the range called by the macro I have:

K:\XXX\XXX\[oldfilename.xlsx]

and

YYY\[new filename.xlsx] to place the new file in a nested folder within
the active workbook.

I've tried putting apostrophes and with and without brackets and all that,
but with no luck.

Thanks.


"Joel" wrote:

Sub Copyfiles()

Set fscopy = CreateObject("Scripting.FileSystemObject")
RowCount = 1
Do While Range("A" & RowCount) < ""
OldName = Range("A" & RowCount)
NewName = Range("B" & RowCount)
fscopy.CopyFile OldName, NewName

RowCount = RowCount + 1
Loop

End Sub


"ajd" wrote:

I'd like to copy an existing workbook (that's closed, preferably) and name
the copy based on cell values. So, for example in column A of the active
workbook I have a list of different excel workbooks that are closed (with the
file path), and in column B I have a list of names that I want to call the
copied workbooks. For all of the items in the list I'd like to copy the
respective workbook and give it the listed name. I figure I need a VBA loop,
but have no idea on the commands needed. Thanks.

The code should work just fine for you if you leave it as it was posted,
the code posted doesn't change the filepath (which is what you are
having a problem with "K:\xx....etc"), do you need it saved to a
different filepath?, try to be a little more descriptive of your need so
we can provide you with a satisfactory answer.

ajd;213833 Wrote:
Thanks. I can't get the macro to work though because it has a "file not
found" error. What format does the link to the old file and the name of
the
new file need to be in?

Right now within the range called by the macro I have:

K:\XXX\XXX\[oldfilename.xlsx]

and

YYY\[new filename.xlsx] to place the new file in a nested folder within
the active workbook.

I've tried putting apostrophes and with and without brackets and all
that,
but with no luck.

Thanks.


"Joel" wrote:

Sub Copyfiles()

Set fscopy = CreateObject("Scripting.FileSystemObject")
RowCount = 1
Do While Range("A" & RowCount) < ""
OldName = Range("A" & RowCount)
NewName = Range("B" & RowCount)
fscopy.CopyFile OldName, NewName

RowCount = RowCount + 1
Loop

End Sub


"ajd" wrote:

I'd like to copy an existing workbook (that's closed, preferably)
and name
the copy based on cell values. So, for example in column A of the
active
workbook I have a list of different excel workbooks that are closed
(with the
file path), and in column B I have a list of names that I want to
call the
copied workbooks. For all of the items in the list I'd like to copy
the
respective workbook and give it the listed name. I figure I need a
VBA loop,
but have no idea on the commands needed. Thanks.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=58645

The original files are all in the same folder with a path similar to:

K:\XXX\YYY\

and are .xlsx files

The active workbook with the list of original files to copy and the list new
of names is in a path similar to:

K:\ZZZ\AAA\ (ie. totally different location)

I'd like the program to copy the appropriate file (named in Column A of the
active sheet) in the K:\XXX\YYY\ folder and paste it in a folder with the
path: K:\ZZZ\AAA\BBB with the name listed in Column B.

While I think the code provided will work, for whatever reason it isn't
recognizing the file path that I have listed in Column A. The file path I
have listed in column A is K:\XXX\YYY\[original file name1.xlsx]. The new
file name I have listed is BBB\[new file name1.xlsx]

The error is "file not found", so I'm guessing it has to do with the
original file name path.

Thanks again.


"Simon Lloyd" wrote:


The code should work just fine for you if you leave it as it was posted,
the code posted doesn't change the filepath (which is what you are
having a problem with "K:\xx....etc"), do you need it saved to a
different filepath?, try to be a little more descriptive of your need so
we can provide you with a satisfactory answer.

ajd;213833 Wrote:
Thanks. I can't get the macro to work though because it has a "file not
found" error. What format does the link to the old file and the name of
the
new file need to be in?

Right now within the range called by the macro I have:

K:\XXX\XXX\[oldfilename.xlsx]

and

YYY\[new filename.xlsx] to place the new file in a nested folder within
the active workbook.

I've tried putting apostrophes and with and without brackets and all
that,
but with no luck.

Thanks.


"Joel" wrote:

Sub Copyfiles()

Set fscopy = CreateObject("Scripting.FileSystemObject")
RowCount = 1
Do While Range("A" & RowCount) < ""
OldName = Range("A" & RowCount)
NewName = Range("B" & RowCount)
fscopy.CopyFile OldName, NewName

RowCount = RowCount + 1
Loop

End Sub


"ajd" wrote:

I'd like to copy an existing workbook (that's closed, preferably)
and name
the copy based on cell values. So, for example in column A of the
active
workbook I have a list of different excel workbooks that are closed
(with the
file path), and in column B I have a list of names that I want to
call the
copied workbooks. For all of the items in the list I'd like to copy
the
respective workbook and give it the listed name. I figure I need a
VBA loop,
but have no idea on the commands needed. Thanks.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=58645

Never mind, I figured out a workaround. And apparently the brackets aren't
needed. Thanks.

"ajd" wrote:

The original files are all in the same folder with a path similar to:

K:\XXX\YYY\

and are .xlsx files

The active workbook with the list of original files to copy and the list new
of names is in a path similar to:

K:\ZZZ\AAA\ (ie. totally different location)

I'd like the program to copy the appropriate file (named in Column A of the
active sheet) in the K:\XXX\YYY\ folder and paste it in a folder with the
path: K:\ZZZ\AAA\BBB with the name listed in Column B.

While I think the code provided will work, for whatever reason it isn't
recognizing the file path that I have listed in Column A. The file path I
have listed in column A is K:\XXX\YYY\[original file name1.xlsx]. The new
file name I have listed is BBB\[new file name1.xlsx]

The error is "file not found", so I'm guessing it has to do with the
original file name path.

Thanks again.


"Simon Lloyd" wrote:


The code should work just fine for you if you leave it as it was posted,
the code posted doesn't change the filepath (which is what you are
having a problem with "K:\xx....etc"), do you need it saved to a
different filepath?, try to be a little more descriptive of your need so
we can provide you with a satisfactory answer.

ajd;213833 Wrote:
Thanks. I can't get the macro to work though because it has a "file not
found" error. What format does the link to the old file and the name of
the
new file need to be in?

Right now within the range called by the macro I have:

K:\XXX\XXX\[oldfilename.xlsx]

and

YYY\[new filename.xlsx] to place the new file in a nested folder within
the active workbook.

I've tried putting apostrophes and with and without brackets and all
that,
but with no luck.

Thanks.


"Joel" wrote:

Sub Copyfiles()

Set fscopy = CreateObject("Scripting.FileSystemObject")
RowCount = 1
Do While Range("A" & RowCount) < ""
OldName = Range("A" & RowCount)
NewName = Range("B" & RowCount)
fscopy.CopyFile OldName, NewName

RowCount = RowCount + 1
Loop

End Sub


"ajd" wrote:

I'd like to copy an existing workbook (that's closed, preferably)
and name
the copy based on cell values. So, for example in column A of the
active
workbook I have a list of different excel workbooks that are closed
(with the
file path), and in column B I have a list of names that I want to
call the
copied workbooks. For all of the items in the list I'd like to copy
the
respective workbook and give it the listed name. I figure I need a
VBA loop,
but have no idea on the commands needed. Thanks.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=58645