Select cells in a worksheet, import to another worksheet, sort on criteria, and create and send a spreadsheet as an email attachment

[juscuz419]

I have a workbook that I need to select rows that have cells numbered in one worksheet, import them into another worksheet, sort them based on a value in one column,

The first worksheet can be 150 rows long and would look like the table below (with data in columns C through O too)

A

Role

C

D

E

F

G

H

I

J

K

L

M

N

O

1

AM

A

3

TL1

A

5

A

4

D

D

2

D

D

A

7

TL2

A

8

D

A

6

D

I need it to import into a second worksheet with the results to look like this (with the C through O data too)

A

Role

C

D

E

F

G

H

I

J

K

L

M

N

O

1

AM

2

D

3

TL1

4

D

5

A

6

D

7

TL2

8

D

And then create a spreadsheet of the result, open outlook, attach the new sheet, and allow the input of the addressees.

I've tried for a while but this has me befuddled.

 

[DanteAmor]

Try this:

Sub Create_And_Send_Sheet()
  Dim sh2 As Worksheet
  Dim pathFile As String
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  pathFile = ThisWorkbook.Path & "\" & "File.xlsx"  'fit to the name of new workbook
  Sheets("Sheet1").Copy                             'fit to the name of sheet to send
  Set sh2 = ActiveSheet
  
  sh2.Range("A:O").Sort sh2.Range("A1"), xlAscending, Header:=xlYes
  sh2.Range("A" & sh2.Range("A" & Rows.Count).End(3).Row + 1 & ":O" & Rows.Count).Clear
  sh2.Parent.SaveAs pathFile, xlOpenXMLWorkbook
  sh2.Parent.Close False
   
  With CreateObject("Outlook.Application").CreateItem(0)
    .To = "example@gmail.com"
    .Subject = "This is the Subject line"
    .Body = "This is the BODY line"
    .Attachments.Add pathFile
    .Display
  End With
  
  Application.ScreenUpdating = False
End Sub

 

[juscuz419]

Got called away. Sorry for the delay. Worked like a charm. You have helped me with issues in the past. You are great. Thanks