Splitting a file into multiple workbooks

C

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
 
R

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
 
R

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top