Splitting a file into multiple workbooks

[cardfan3206]

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

 

[Ron de Bruin]

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

 

[Ron de Bruin]

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" & 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
Before:=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 Before:=Workbooks(WSNew.Parent.Name).Sheets(1)