Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split a workbook into several using cell value for filenames
Hi First time poster and novice when it comes to Macro writing here so please be gentle!... :) What I need to do is as follows; I have a workbook with 3 sheets, titled; "Prod" , "Die" , "Job" Based on a value in a cell in "Prod"; eg A1: "P2380"I need to do the following. 1. Create a new folder in a predetermined location named after the cell value. 2. Split the workbook into 3 separate files in this new folder using the naming convention CellValue_SheetName.xls; -e.g. P2380_Prod.xls P2380_Die.xls P2380_Job.xls-. 3. Close and Delete the original file. I'm going to have a go at it, but my knowledge of VB is very limited..... thanks for any help you can give Kev -- kevinho ------------------------------------------------------------------------ kevinho's Profile: http://www.excelforum.com/member.php...o&userid=24984 View this thread: http://www.excelforum.com/showthread...hreadid=385171 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split a workbook into several using cell value for filenames
Try this:
Sub DoStuff() Const cFolderRoot As String = "C:\Documents and Settings\MyID\My Documents\" Dim strKey As String, strFolder As String, strMainPath as string Dim ws As Worksheet, wbMain As Workbook Set wbMain = ActiveWorkbook 'get pointer to active workbook strMainPath = wbMain.FullName strKey = ActiveWorkbook.Sheets("Prod").Range("A1").Formula strFolder = cFolderRoot & strKey MkDir strFolder 'create new folder For Each ws In wbMain.Worksheets 'loop through each worksheet in wbMain, setting ws as a pointer to the current worksheet within the loop ws.Copy 'copy this worksheet into new workbook ActiveWorkbook.SaveAs Filename:=strFolder & "\" & strKey & "_" & ws.Name & ".xls" ActiveWorkbook.Close savechanges:=False Next ws wbMain.Close savechanges:=False Kill strMainPath End Sub Not that this code must be run from a separate workbook or add-in as you cannot delete the workbook that you are running the macro from. Cheers, Dave "kevinho" wrote: Hi First time poster and novice when it comes to Macro writing here so please be gentle!... :) What I need to do is as follows; I have a workbook with 3 sheets, titled; "Prod" , "Die" , "Job" Based on a value in a cell in "Prod"; eg A1: "P2380"I need to do the following. 1. Create a new folder in a predetermined location named after the cell value. 2. Split the workbook into 3 separate files in this new folder using the naming convention CellValue_SheetName.xls; -e.g. P2380_Prod.xls P2380_Die.xls P2380_Job.xls-. 3. Close and Delete the original file. I'm going to have a go at it, but my knowledge of VB is very limited..... thanks for any help you can give Kev -- kevinho ------------------------------------------------------------------------ kevinho's Profile: http://www.excelforum.com/member.php...o&userid=24984 View this thread: http://www.excelforum.com/showthread...hreadid=385171 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split a workbook into several using cell value for filenames
"Dave Ramage" wrote in message ... Not that this code must be run from a separate workbook or add-in as you cannot delete the workbook that you are running the macro from. You can try this from the same workbook With wbMain .ChangeFileAccess xlReadOnly Kill .FullName .Close SaveChanges:=False End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split a workbook into several using cell value for filenames
Thanks guys, perfect result Ke -- kevinh ----------------------------------------------------------------------- kevinho's Profile: http://www.excelforum.com/member.php...fo&userid=2498 View this thread: http://www.excelforum.com/showthread.php?threadid=38517 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook split into two after opening | Excel Discussion (Misc queries) | |||
Using cell value in filenames | Excel Worksheet Functions | |||
Split Workbook part 2 | Excel Programming | |||
Split workbook | Excel Programming | |||
split data in many workbook | Excel Programming |