Save 1 file to different folders based on cell.value
 

Hi,
I need to save 1 workbook into different folders based on cell.value
The folders are already created in a directory with a customer number (5
digits).

On the workbook, the customer number is saved in E:1. If more than one
customer, the values are comma separated.

Based on these values, the macro should find the folder for each customer
and save the file on that folder adding today's date to the sheet name.
Last, delete the original file (It is saved in a temp folder)

any ideas I will greatly appreciate. Thanks
Gaba

Save 1 file to different folders based on cell.value
 

Try code like the following:

Sub AAA()
Dim V As Variant
Dim N As Long
Dim FileName As String
V = Split(Range("E1"), ",")

For N = LBound(V) To UBound(V)
FileName = "C:\" & CStr(V(N)) & "\" & ThisWorkbook.Name
On Error Resume Next
Kill FileName
On Error GoTo 0
ThisWorkbook.SaveCopyAs FileName
Next N
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 25 Feb 2009 14:57:02 -0800, Gaba
wrote:

Hi,
I need to save 1 workbook into different folders based on cell.value
The folders are already created in a directory with a customer number (5
digits).

On the workbook, the customer number is saved in E:1. If more than one
customer, the values are comma separated.

Based on these values, the macro should find the folder for each customer
and save the file on that folder adding today's date to the sheet name.
Last, delete the original file (It is saved in a temp folder)

any ideas I will greatly appreciate. Thanks
Gaba

Save 1 file to different folders based on cell.value
 

Thanks Chip for your quick answer. I'm getting an error 400 (?) it is saving
only the first value when I have more than one (comma separated).
If I have only one value, no error, no problem.
Any ideas?
Thanks in advance,
Gaba

"Chip Pearson" wrote:

Try code like the following:

Sub AAA()
Dim V As Variant
Dim N As Long
Dim FileName As String
V = Split(Range("E1"), ",")

For N = LBound(V) To UBound(V)
FileName = "C:\" & CStr(V(N)) & "\" & ThisWorkbook.Name
On Error Resume Next
Kill FileName
On Error GoTo 0
ThisWorkbook.SaveCopyAs FileName
Next N
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)