I have been using the following code to split an excel file into multiple
workbooks.

http://www.rondebruin.nl/copy5_3.htm

It works great but I want to see if I can get it to do a couple additional
things.
1. I have a series of header rows that I would like to copy to each workbook
that is created.
2. I have 2 worksheets in the master workbook that I would like to copy into
each new workbook.
3. I have done some custom formatting and embedded some formulae that I
would like to copy instead of pasting values only.

Is this doable?

Tracey

Hi Tracey

I believe you mailed me private also about this
1 and 2 is no problem

3 will give problems

I will create a example this weekend for you


--

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


"cardfan3206" wrote in message
...
I have been using the following code to split an excel file into multiple
workbooks.

http://www.rondebruin.nl/copy5_3.htm

It works great but I want to see if I can get it to do a couple additional
things.
1. I have a series of header rows that I would like to copy to each
workbook
that is created.
2. I have 2 worksheets in the master workbook that I would like to copy
into
each new workbook.
3. I have done some custom formatting and embedded some formulae that I
would like to copy instead of pasting values only.

Is this doable?

Tracey

Hi Tracey

Add a few lines to the code

If your data start in A4 (header row) for example you set the range like
this
Set My_Range = Range("A4:D" & LastRow(ActiveSheet))


To copy tow header rows above your data it use
My_Range.Parent.Range("A1:Z2").Copy WSNew.Cells(1)

To copy the two other sheets use
My_Range.Parent.Parent.Sheets(Array("MySheet1", "Mysheet2")).Copy
Befo=Workbooks(WSNew.Parent.Name).Sheets(1)


This is where you must copy the lines

Else
'Add new workbook with one sheet
Set WSNew = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
My_Range.Parent.Range("A1:Z2").Copy WSNew.Cells(1)

'Copy/paste the visible data to the new workbook
My_Range.SpecialCells(xlCellTypeVisible).Copy
With WSNew.Range("A4")
' Paste:=8 will copy the columnwidth in Excel 2000 and
higher
' Remove this line if you use Excel 97
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

My_Range.Parent.Parent.Sheets(Array("MySheet1",
"MySheet2")).Copy Befo=Workbooks(WSNew.Parent.Name).Sheets(1)


--

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


"Ron de Bruin" wrote in message
...
Hi Tracey

I believe you mailed me private also about this
1 and 2 is no problem

3 will give problems

I will create a example this weekend for you


--

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


"cardfan3206" wrote in message
...
I have been using the following code to split an excel file into multiple
workbooks.

http://www.rondebruin.nl/copy5_3.htm

It works great but I want to see if I can get it to do a couple
additional
things.
1. I have a series of header rows that I would like to copy to each
workbook
that is created.
2. I have 2 worksheets in the master workbook that I would like to copy
into
each new workbook.
3. I have done some custom formatting and embedded some formulae that I
would like to copy instead of pasting values only.

Is this doable?

Tracey