+ Reply to Thread
Results 1 to 5 of 5

How to select pivot table range?

  1. #1
    Registered User
    Join Date
    05-12-2020
    Location
    Lithuania
    MS-Off Ver
    2016y.
    Posts
    2

    How to select pivot table range?

    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

  2. #2
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to select pivot table range?

    Can send a short excel sample or at list a screen shot to show the issue.
    Pivot table ranges from A2:J296
    is it the range used by the Pivot Table or is it the range covers by the table display ?
    - Battle without fear gives no glory - Just try

  3. #3
    Registered User
    Join Date
    05-12-2020
    Location
    Lithuania
    MS-Off Ver
    2016y.
    Posts
    2

    Re: How to select pivot table range?

    It is the range of table display, but it could change a little because of input data, and I need to select and send the whole table,
    just with a couple of hidden columns(The ones that i manually hide).
    Data input is on the other sheet.

  4. #4
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to select pivot table range?

    Will you please spend some minutes to read forum's rules , overall the second one about code tags ...!

  5. #5
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: How to select pivot table range?

    Here a piece of code which make the selection of PivotTable1
    You may adjust it to do a copy .
    If you need more we need Excel sample file

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to select multiple pivot table filters, based on values in a table
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2018, 06:42 PM
  2. [SOLVED] Select Automatic Report Filter in Pivot where Filter exist in Data table else select Blank
    By vinaynaran in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-02-2016, 08:47 AM
  3. Pivot Table select all Pivot Items not working as intended
    By rasonline in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2014, 05:22 PM
  4. select range of pivot table to change number format
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-31-2012, 01:44 PM
  5. Pivot Table-Select Pivot Items
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2011, 01:20 PM
  6. Return pivot table range...not the data table, the PIVOT TABLE!
    By Air_Cooled_Nut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2008, 01:07 PM
  7. [SOLVED] Pivot Table Select Range of Value
    By pa32rt300t in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2005, 12:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1