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

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

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

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

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

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

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 -