Code to split Worksheets into seperat workbooks

[Phil Smith]

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

 

[joel]

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

 

[Jarek Kujawa]

"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

 

[Ron de Bruin]

See this example if i understand you correct
http://www.rondebruin.nl/copy6.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

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

 

[Phil Smith]

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

 

[Phil Smith]

Works perfectly. Absolutely exactly what I needed as is. Thank you
very much. I truly appreciate it.

 

[Jarek Kujawa]

....then you might close your original file without saving...