Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SAVE & SEND A FILE BASED ON TEXT IN A CELL VIA MACRO | New Users to Excel | |||
How to copy a file into folders that are created based on a list? | Excel Programming | |||
Automatically save file based on cell value | Excel Discussion (Misc queries) | |||
Save as to two folders | Excel Programming | |||
Save as to two folders | Excel Programming |