| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to split Worksheets into seperat workbooks
It would take me forever to figure out how to code this myself. What I
want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to split Worksheets into seperat workbooks
I'm saving the newbook under the sheet name in the default directory.
Sub Splitbook() MyPath = ThisWorkbook.Path For Each sht In ThisWorkbook.Sheets sht.Copy ActiveSheet.Cells.Copy ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats ActiveWorkbook.SaveAs _ Filename:=MyPath & "\" & sht.Name & ".xls" ActiveWorkbook.Close savechanges:=False Next sht End Sub "Phil Smith" wrote: It would take me forever to figure out how to code this myself. What I want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to split Worksheets into seperat workbooks
"to take a workbook with a dozen worksheets, and create 12 seperate
worksheets"? you mean "create 12 seperate WORKBOOKS"? 1 worksheet in each of them? Sub Separate Dim ws as Worksheet Sheets.Add ActiveSheet.Name = "test" For Each ws in Activeworkbook.Worksheets If ws.Name < "test" Then With Range(Cells(1,1), ActiveCell.SpecialCells(xlLastCell)) ..Copy ..PasteSpecial Paste:=xlPasteValues End With ws.Move End If Next ws End Sub On 25 Mar, 17:58, Phil Smith wrote: It would take me forever to figure out how to code this myself. *What I want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to split Worksheets into seperat workbooks
See this example if i understand you correct
http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Phil Smith" wrote in message ... It would take me forever to figure out how to code this myself. What I want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil __________ Information from ESET Smart Security, version of virus signature database 3962 (20090325) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 3962 (20090325) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to split Worksheets into seperat workbooks
This takes my current workbook, converts all of the sheets to thier
values. My original workbook needs to remain intact with thier links. It does tell me how to accomplish some of my goal though. Thanx Jarek Kujawa wrote: "to take a workbook with a dozen worksheets, and create 12 seperate worksheets"? you mean "create 12 seperate WORKBOOKS"? 1 worksheet in each of them? Sub Separate Dim ws as Worksheet Sheets.Add ActiveSheet.Name = "test" For Each ws in Activeworkbook.Worksheets If ws.Name < "test" Then With Range(Cells(1,1), ActiveCell.SpecialCells(xlLastCell)) .Copy .PasteSpecial Paste:=xlPasteValues End With ws.Move End If Next ws End Sub On 25 Mar, 17:58, Phil Smith wrote: It would take me forever to figure out how to code this myself. What I want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to split Worksheets into seperat workbooks
Works perfectly. Absolutely exactly what I needed as is. Thank you
very much. I truly appreciate it. joel wrote: I'm saving the newbook under the sheet name in the default directory. Sub Splitbook() MyPath = ThisWorkbook.Path For Each sht In ThisWorkbook.Sheets sht.Copy ActiveSheet.Cells.Copy ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats ActiveWorkbook.SaveAs _ Filename:=MyPath & "\" & sht.Name & ".xls" ActiveWorkbook.Close savechanges:=False Next sht End Sub "Phil Smith" wrote: It would take me forever to figure out how to code this myself. What I want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Code to split Worksheets into seperat workbooks
....then you might close your original file without saving...
On 25 Mar, 23:55, Phil Smith wrote: This takes my current workbook, converts all of the sheets to thier values. *My original workbook needs to remain intact with thier links. It does tell me how to accomplish some of my goal though. Thanx Jarek Kujawa wrote: "to take a workbook with a dozen worksheets, and create 12 seperate worksheets"? you mean "create 12 seperate WORKBOOKS"? 1 worksheet in each of them? Sub Separate Dim ws as Worksheet Sheets.Add ActiveSheet.Name = "test" For Each ws in Activeworkbook.Worksheets If ws.Name < "test" Then With Range(Cells(1,1), ActiveCell.SpecialCells(xlLastCell)) .Copy .PasteSpecial Paste:=xlPasteValues End With ws.Move End If Next ws End Sub On 25 Mar, 17:58, Phil Smith wrote: It would take me forever to figure out how to code this myself. *What I want is to take a workbook with a dozen worksheets, and create 12 seperate worksheets, filled with the formatting and the values, (similar to a paste special values only) of each worksheet. I need the values only because I am using a lot of links to create the worksheets. Can anyone point me to some code I can hack up? Thanx Phil- Ukryj cytowany tekst - - Pokaż cytowany tekst - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Answer to Saving worksheets as individual workbooks using code? | Excel Programming | |||
| Split email address into seperat columns | Excel Worksheet Functions | |||
| Can I split worksheets from one workbook into individual workbooks | Excel Discussion (Misc queries) | |||
| Change code to make worksheets instead of workbooks | Excel Programming | |||
| VBA code for looping through open workbooks and worksheets | Excel Programming | |||
Linear Mode
