Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Excel VBA to select date range for a pivot table

    I'd like to incorporate some VBA into an already existing macro. I've tried to create parts of the new code by recording some steps. The existing code for an already functioning worksheet uses data exported from a payroll app, but has many more columns than needed. So far I can delete the unneeded columns, but that messes up the pivot tables. My goal for this next step is to try to recreate/rename the pivot table.

    These two snippets represent exactly what I want. But the second part only works for the data exported today. How do I get the "=Sheet1!R7C7:R408C43" to become dynamic so whatever range selected in the first macro is used in the second? Or is there another solution?

    ==========================

    Sub SelectRangeForPivotTable()
    'Selects remaining data after excess columns have been deleted
    Range("g7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    End Sub

    ==========================

    Sub Assign_andNamePivot()
    ' Assign_andNamePivot table
    ActiveWorkbook.Names.Add Name:="PivotTable_OT1", RefersToR1C1:= _
    "=Sheet1!R7C7:R408C43"
    ActiveWorkbook.Names("PivotTable_OT1").Comment = ""
    End Sub

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Oops! Thread title should read "Excel VBA to select DATA range for a pivot table"

    Anyway, this get me kind of close:

    ActiveWorkbook.Names.Add Name:="PivotTable_OT1", RefersToR1C1:=ActiveSheet.Range("g7").CurrentRegio n.Select

    And by that I mean that it doesn't choke and actually creates a pivot with the selected name. But the range is not selected, just says "=TRUE".
    Last edited by Arcturus16a; 2012-02-28 at 20:41.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi

    You could do it in one go:

    Sub SelectRangeForPivotTable()
    'Selects remaining data after excess columns have been deleted
    Range("g7").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Name = "PivotTable_OT1"


    End Sub

    However, when I do a little test, your line..
    Range(Selection, Selection.End(xlToRight)).Select
    ..selects a range extending to the very last column in my test sheet.

    How about trying something like:

    Sub temp()
    [g7].CurrentRegion.Name = "PivotTable_OT1"
    End Sub

    ..i.e. providing [g7].CurrentRegion correctly selects your range.

    zeddy

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Outstanding. Many thanks to you Zeddy.

    After deleting the unneeded columns my spreadsheet shrinks to about half its original size, but the range is contiguous, so both solutions work equally well.
    Last edited by Arcturus16a; 2012-02-29 at 13:21.

Posting Permissions

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