How to select pivot table range, dynamically

[lukisx7]

Hello,



I have a few excel pivot tables in different sheets, which I need to be sent automatically through outlook.

How could I select the range needed, as I need to send the whole pivot table in the active sheet, but without columns that I hide.



My current Pivot table ranges from A2:J296, with D;E;F;G columns hidden. But the thing is, in other sheets, there could be less columns, less rows.

Maybe there is some way to select the columns range by the text written in the last column? (In J2 in this particular case).



Thank you!



My current code, looks like this (But it still sends with all the hidden columns, and the select range is also not dynamic):

Sub Send()

Dim x, y As String

  

   ActiveSheet.Range("A2").Select

   ActiveSheet.Range(Selection, Selection.End(xlToRight).End(xlToRight).End(xlToRight)).Select

   ActiveSheet.Range(Selection, Selection.End(xlDown).End(xlDown)).Select

  

   x = ActiveSheet.Range("M1").Value

   y = ActiveSheet.Range("M2").Value

  

   ActiveWorkbook.EnvelopeVisible = True

     

   With ActiveSheet.MailEnvelope

      .Item.to = x

      .Item.cc = y

      .Item.Subject = "Client debts " & Date

      .Item.Send

   End With

End Sub

 

[Domenic]

One way would be to add a temporary worksheet, copy and pivot table to that worksheet, delete the columns that you don't want, send it, and then delete the temporary worksheet. However, if you have Outlook, you can use the following macro to send an email. For testing purposes, though, it displays the email instead of sending it. Once you've tested it, simply replace .Display with .Send to actually send the email.

Sub Send()

    Dim pivotTableRange As Range
    Set pivotTableRange = Range("A2").CurrentRegion
    
    Dim strTo As String
    strTo = Range("M1").Value
    
    Dim strCC As String
    strCC = Range("M2").Value
    
    Dim strSubject As String
    strSubject = "Client debts " & Date
    
    pivotTableRange.Copy
    
    With CreateObject("Outlook.Application").CreateItem(0)
        .to = strTo
        .cc = strCC
        .Subject = strSubject
        .GetInspector.WordEditor.Application.Selection.Paste
        .display 'or .send
    End With
    
    Set pivotTableRange = Nothing

End Sub

Hope this helps!

 

[Norie]

You could use the properties, e.g. DataBodyRange, of the pivot table(s) to get the range.

Sub Send()
Dim pt As PivotTable
Dim ptRange As Range
Dim strTo As String
Dim strCC As String
Dim strSubject As String

    Set pt = Sheets("Sheet1").PivotTables(1)
    
    Set ptRange = pt.DataBodyRange.CurrentRegion
    
    strTo = Range("M1").Value
       
    strCC = Range("M2").Value
     
    strSubject = "Client debts " & Date
    
    ptRange.SpecialCells(xlCellTypeVisible).Copy
    
    With CreateObject("Outlook.Application").CreateItem(0)
        .to = strTo
        .cc = strCC
        .Subject = strSubject
        .GetInspector.WordEditor.Application.Selection.Paste
        .display 'or .send
    End With
    
    Set ptRange = Nothing

End Sub

 

[Domenic]

As @Norie has pointed out, you can refer to the pivot table itself for the desired range. I should have done the same. Note, though, you can also refer to the whole pviot table, including any page fields, as follows...

Set ptRange = pt.TableRange2

Or, to exclude page fields...

Set ptRange = pt.TableRange1