[RESOLVED] additional tab

[kris01]

Hi

I have the below code which copies a tab called Mainstay Master. Creates a xls then sends to a particular mail group with an attachment.

Where do I add the code if I want to copy another tab called 'Mainstay Report' and include this in the same attachment as 'Mainstay Master. I.e my email attachment has two tabs included.

The two tabs Mainstay master and Mainstay report are situated in the same file to copy.



Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
Dim mypath As String, myfile As String, scc As String, sto As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports"
With Sheets("Control")
sto = Join(WorksheetFunction.Transpose(Range("Mail_to")), ";")
scc = Join(WorksheetFunction.Transpose(Range("Mail_cc")), ";")
End With
Set w = Sheets("Mainstay Master")
Set b = Workbooks.Add
w.Copy b.Sheets(1)
myfile = mypath & Format(Date, "MMM") & " Mainstay Master.xlsx"
Application.DisplayAlerts = False
For sh = b.Sheets.Count To 2 Step -1
b.Sheets(sh).Delete
Next
Application.DisplayAlerts = True
b.SaveAs myfile
Set ol = CreateObject("outlook.application")
Set msg = ol.Createitem(0)
With msg
.To = sto
.cc = scc
.Subject = "Mainstay Report & Master file"
.Body = "Good Morning"
.attachments.Add myfile
.display
End With

Workbooks("Freehold Manager Template V1").Sheets("Control").Activate
Range("TimeStamp11").Value = Format(Now, "dd/mm/yy HH:mm:SS")

[westconn1]

you can try like

Set w = Sheets("Mainstay Master")
set x = sheets("Mainstay Report")
Set b = Workbooks.Add
w.Copy b.Sheets(1)
x.copy b.sheets(,1)

you would also need to change to

For sh = b.Sheets.Count To 3 Step -1

[kris01]

Breaks on this line....

compile error

Argument not optional.

x.copy b.sheets(,1)

[westconn1]

try

x.copy ,b.sheets(1)

sorry comma in the wrong place

[kris01]

Perfect. Thank you.