New VBA script

[vman5781]

All,

I have a spreadsheet (containing expense report info) 14 columns wide by infinite rows, at this point I have it set up with a Pivot and slicers

I would like to set up a Macro that would automatically "pull the corresponding info" per the selection of Manager's name (which would be column 12 (O), and then email it to said manager. Have the info come over

Expense type (column 4 (D)) ----Amount --(column 8(L)) and this info be expanded as desired

Any help would be appreciated.

Thank you

 

[Logit]

.
Can you post an example of your workbook to a 'cloud' site ? Something like DropBox.com, Amazon's cloud site, Google cloud site ?
Don't include any confidential information but have just enough examples on the sheet to understand what is occurring ?

Also, what do you mean by : " and this info be expanded as desired " ?

 

[vman5781]

I will Try to do this from home this weekend

The pivot shows collapsed -- and I would like them to be able to expand to show more details ie: employee, Vendor or others as needed (or would it do that like the original pivot?) Also, email address would be in column (C)

 

[vman5781]

Also are the cloud sites free?

 

[DanteAmor]

Try this.
I already have a form for these cases, the information is saved in a file and the file can be sent by outlook.

Create a sheet named "temp"
Change data in red by your information.

Sub Split_Data()
'----
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.StatusBar = False
    Set l1 = ThisWorkbook
    Set h1 = Sheets("[COLOR=#ff0000]expense report[/COLOR]")    'data sheet
    Set h2 = Sheets("[COLOR=#ff0000]temp[/COLOR]")     'temporal sheet
    col = "[COLOR=#ff0000]O[/COLOR]"                   'Manager's name column
    ucol = "[COLOR=#ff0000]N[/COLOR]"                  'last column
    n = Columns(col).Column
    '
    h2.Cells.Clear
    If h1.AutoFilterMode Then h1.AutoFilterMode = False
    h1.Columns(col).Copy h2.[A1]
    u2 = h2.Range("A" & Rows.Count).End(xlUp).Row
    h2.Range("A1:A" & u2).RemoveDuplicates Columns:=1, Header:=xlYes
    '
    ruta = l1.Path & "\"
    u2 = h2.Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To u2
        Application.StatusBar = "Generando archivo " & i - 1 & " de " & u2 - 1
        clave = h2.Cells(i, "A")
        If h1.AutoFilterMode Then h1.AutoFilterMode = False
        u1 = h1.Range("A" & Rows.Count).End(xlUp).Row
        h1.Range("A1:" & ucol & u1).AutoFilter Field:=n, Criteria1:=clave
        Set l2 = Workbooks.Add
        Set h21 = l2.Sheets(1)
        h1.Range("A1:" & ucol & u1).Copy h21.[A1]
        l2.SaveAs ruta & clave
        l2.Close
        '
        'send mail by outlook
        Set dam = CreateObject("outlook.application").createitem(0)
        dam.To = "[COLOR=#ff0000]mailto@gmail.com[/COLOR]"
        dam.Subject = "[COLOR=#ff0000]Expenses[/COLOR]"
        dam.Body = "[COLOR=#ff0000]Some data[/COLOR]"
        dam.Attachments.Add ruta & clave
        'dam.Send 'send mail
        dam.Display 'display mail
    Next
    If h1.AutoFilterMode Then h1.AutoFilterMode = False
    Application.StatusBar = False
    MsgBox "Archivos creados"
End Sub

 

[Logit]

.
Most of the cloud sites mentioned are free.