copy specific sheet (value come from range) to new workbook

[speed-star]

Hi All,

could anyone help me
I need macro to copy one sheet into new workbook and attach it in outlook,
I found this code, but unfortunately this code was copied all sheets in the workbook.
instead of one sheet that I want.

let say that selected sheet I want to choose is only in range "rangeSheet".



'Set up for some shorthand
Set wsTarget = ActiveWorkbook.Worksheets(strSheetEmail)
strFilePath = ActiveWorkbook.Path & Application.PathSeparator

'Retrieve the sheets to print
For Each cl In wsTarget.Range("rangeSheet")
sSheetsToPrint = sSheetsToPrint & cl.Value & ","
Next cl
sSheetsToPrint = Left(sSheetsToPrint, Len(sSheetsToPrint) - 1)
sSheets() = Split(sSheetsToPrint, ",")

Set srcWB = ActiveWorkbook
For Each sht In srcWB.Sheets
sht.Copy
Set destWB = ActiveWorkbook
destWB.SaveAs strFilePath & strSheetPrefix & sht.Name & ".xlsx"
destWB.Close
Next

 

[Mike H..]

Hi All,

could anyone help me
I need macro to copy one sheet into new workbook and attach it in outlook,
I found this code, but unfortunately this code was copied all sheets in the workbook.
instead of one sheet that I want.

let say that selected sheet I want to choose is only in range "rangeSheet".



'Set up for some shorthand
Set wsTarget = ActiveWorkbook.Worksheets(strSheetEmail)
strFilePath = ActiveWorkbook.Path & Application.PathSeparator

'Retrieve the sheets to print
For Each cl In wsTarget.Range("rangeSheet")
sSheetsToPrint = sSheetsToPrint & cl.Value & ","
Next cl
sSheetsToPrint = Left(sSheetsToPrint, Len(sSheetsToPrint) - 1)
sSheets() = Split(sSheetsToPrint, ",")

Set srcWB = ActiveWorkbook
For Each sht In srcWB.Sheets
sht.Copy
Set destWB = ActiveWorkbook
destWB.SaveAs strFilePath & strSheetPrefix & sht.Name & ".xlsx"
destWB.Close
Next

Hi,

Have a look at this code.

http://www.rondebruin.nl/win/s1/outlook/amail2.htm

 

[speed-star]

thanks Mike,

I can see from your link that is the command will copy the activesheet to new workbook.

the step that I missed is on how to select the spesific sheet as workbook.
let say the sheet that I want to copy and email is in cell A5to A9. If I want to change the selected sheet that I just need to change the value on that range.

 

[speed-star]

Anyone please help