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

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
>

"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 <p...@nhs-inc.com> 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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> 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

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 <p...@nhs-inc.com> 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
>
>

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
>>

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


On 25 Mar, 23:55, Phil Smith <p...@nhs-inc.com> 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 <p...@nhs-inc.com> wrote:
>
> >>It would take me forever to figure out how to code this myself. *WhatI
> >>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 -