Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save records from one file into seperate other files.
I have one master file with five fields
Field1 Field2 Field3 Field4 Filename -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- I need to create one excel file per record. I need to paste A2 data into new file A2 cell, B2 data into new file B2 cell and finally want to save the new file with filaname which is in E2 I need to repeat the entire process for each record. As the output required is in excel i cannot use mailmerge. I tried to record a macro and then put a loop but failed. Please help me to solve this. Thanks a lot in advance.. Boss |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save records from one file into seperate other files.
Boss
This macro will do that for you. I assumed a path to the destination folder of: C:\My Folder Change this as needed. HTH Otto Sub CreateFiles() Dim FileName As String Dim rColA As Range Dim i As Range Dim FullPath As String Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) Application.ScreenUpdating = False For Each i In rColA FileName = i.Offset(, 4).Value FullPath = "C:\My Folder\" & FileName & ".xls" Workbooks.Add i.Resize(, 4).Copy Range("A2") ActiveWorkbook.SaveAs FileName:=FullPath ActiveWorkbook.Close Next i Application.ScreenUpdating = True End Sub "Boss" wrote in message ... I have one master file with five fields Field1 Field2 Field3 Field4 Filename -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- I need to create one excel file per record. I need to paste A2 data into new file A2 cell, B2 data into new file B2 cell and finally want to save the new file with filaname which is in E2 I need to repeat the entire process for each record. As the output required is in excel i cannot use mailmerge. I tried to record a macro and then put a loop but failed. Please help me to solve this. Thanks a lot in advance.. Boss |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save records from one file into seperate other files.
This was HEAVEN...
Thanks a lot.. !! Boss "Otto Moehrbach" wrote: Boss This macro will do that for you. I assumed a path to the destination folder of: C:\My Folder Change this as needed. HTH Otto Sub CreateFiles() Dim FileName As String Dim rColA As Range Dim i As Range Dim FullPath As String Set rColA = Range("A2", Range("A" & Rows.Count).End(xlUp)) Application.ScreenUpdating = False For Each i In rColA FileName = i.Offset(, 4).Value FullPath = "C:\My Folder\" & FileName & ".xls" Workbooks.Add i.Resize(, 4).Copy Range("A2") ActiveWorkbook.SaveAs FileName:=FullPath ActiveWorkbook.Close Next i Application.ScreenUpdating = True End Sub "Boss" wrote in message ... I have one master file with five fields Field1 Field2 Field3 Field4 Filename -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- I need to create one excel file per record. I need to paste A2 data into new file A2 cell, B2 data into new file B2 cell and finally want to save the new file with filaname which is in E2 I need to repeat the entire process for each record. As the output required is in excel i cannot use mailmerge. I tried to record a macro and then put a loop but failed. Please help me to solve this. Thanks a lot in advance.. Boss |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I save/update work to appear in 2 seperate files? | Excel Discussion (Misc queries) | |||
save worksheets as seperate files (I know how to do it one by one) | Excel Discussion (Misc queries) | |||
Save excel pages as seperate files | Excel Programming | |||
Save worksheet to seperate file? | Excel Discussion (Misc queries) | |||
How to save each worksheet as seperate file? | Excel Programming |