Hi,

I've got code to copy worksheets as individual files, however, once the code
is executed and i've got individual files, the page setup is not the same as
the original.

code is below

Sub TEST()
Dim NB, MB As Workbook
Set MB = ThisWorkbook
Dim I, II As Long
For I = 1 To MB.Sheets.Count
Set NB = Workbooks.Add
MB.Sheets(I).Cells.Copy NB.Sheets(1).Range("A1")
NB.Sheets(1).Name = MB.Sheets(I).Name
For II = 2 To NB.Sheets.Count
Application.DisplayAlerts = False
NB.Sheets(2).Delete
Application.DisplayAlerts = True
Next
ActiveWindow.DisplayGridlines = False
NB.SaveAs "C:" & MB.Sheets(I).Name & ".xls"
NB.SendMail ", "Split files"
NB.Close
Next
End Sub

Can you help add a code here which will retain the page setup (page
orientation and margins)

Also, instead of sending email, is it possible to just save the emails in
drafts folder?

thanks in advance

Your code doesn't really copy the worksheets from the master workbook--it copies
the cells in each sheet to a newly created workbook/worksheet.

You have a couple of choices.

You could go through the page setup for the new sheet and make them match the
page setup of the sending sheet. You have to do it for each setting that you
want to keep.

The other option is to copy the sheet to the new workbook--essentially going
through the Edit|Move or copy dialog. This way all the settings will travel
with the worksheet--the rowheight, columnwidths, filters, print layout,
shapes/buttons, and code behind the sheet (worksheet events or code for controls
from the control toolbox toolbar).

If you want to try the second option:

Option Explicit
Sub TEST2()

Dim NB As Workbook
Dim MB As Workbook
Dim I As Long

Set MB = ThisWorkbook

For I = 1 To MB.Sheets.Count
'add a single sheet workbook
Set NB = Workbooks.Add(template:=xlWBATWorksheet)
NB.Worksheets(1).Name = "deletemelater"

MB.Sheets(I).Copy _
after:=NB.Worksheets(1)

Application.DisplayAlerts = False
NB.Worksheets("deletemelater").Delete
Application.DisplayAlerts = True

'not c:\ ?????
NB.SaveAs "C:" & MB.Sheets(I).Name & ".xls"
NB.SendMail ", "Split files"
NB.Close savechanges:=False
Next I
End Sub

ps.
Dim NB, MB As Workbook
declares NB as a variant and MB as a workbook

Same with:
Dim I, II As Long
I is a Variant, II is long.

You could use:

Dim NB as workbook, mb as workbook
dim i as long, ii as long

======
You may want to include what email client you're using to send/save as draft.
It may help others help you.


Ram wrote:

Hi,

I've got code to copy worksheets as individual files, however, once the code
is executed and i've got individual files, the page setup is not the same as
the original.

code is below

Sub TEST()
Dim NB, MB As Workbook
Set MB = ThisWorkbook
Dim I, II As Long
For I = 1 To MB.Sheets.Count
Set NB = Workbooks.Add
MB.Sheets(I).Cells.Copy NB.Sheets(1).Range("A1")
NB.Sheets(1).Name = MB.Sheets(I).Name
For II = 2 To NB.Sheets.Count
Application.DisplayAlerts = False
NB.Sheets(2).Delete
Application.DisplayAlerts = True
Next
ActiveWindow.DisplayGridlines = False
NB.SaveAs "C:" & MB.Sheets(I).Name & ".xls"
NB.SendMail ", "Split files"
NB.Close
Next
End Sub

Can you help add a code here which will retain the page setup (page
orientation and margins)

Also, instead of sending email, is it possible to just save the emails in
drafts folder?

thanks in advance

--

Dave Peterson