Results 1 to 10 of 10

Thread: Pivot tables

  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot tables

    Hi All,
    Please can someone tell me how I can get a Pivot table, which has been recorded with a macro and activated by a command button, to appear in the same sheet (say sheet called "model")instead of adding a new sheet to the workbook
    everytime a user clicks on the command button.

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Rene,

    If you look at the recorded macro you should see something like .CreatePivotTable TableDestination:="", somewhere near the start of the macro. If you change the TableDestination:="" to point to a range on the current sheet, the problem should be solved. For example to have the pivot table start at P5 you could use .CreatePivotTable TableDestination:=Range("P5"), etc. Do not change the rest of the associated statements.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Hi Andrew,
    Thanks for the quick reply. I have a slight problem in when I change the destination to ie. range("p5"), the actual database which is in A1-G100, becomes blank (pivot table in p5 OK). I would like the database to stay put, and I actually would like the pivot table in another constant sheet. In other words, my database is in sheet1 (and it must stay there) but I would like my pivot table to always be in a sheet named "model". So I tried to change table destination"" to table destination=:sheet("model") with no success. VB didn't like that coding at all.

    Please help me out again.

    Thanks a lot

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Hi Andrew,
    Sorry, the database is still in place, but the pivot table went to a new sheet in range("p5").I would, however still like the pivot table to always be in sheet("model").

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    It would be a lot easier to see what is happening if you would post the code from the macro. That would save a lot of time guessing what the macro is doing. I am guessing that there is code in the macro to add a new sheet and activate it before the pivot table is created, but I can't be sure without seeing the code.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Here is just a test database to demonstrate what I want. As you can see from this, everytime I run macro1, a new sheet is inserted in the workbook which contains the pivot table. I renamed sheet3 to be "model", so this part is not done in the macro. I want the pivot table to be in sheet "model" evertime a user activates the command button.
    Attached Files Attached Files

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    I have attached your workbook modified to do what I think that you want. You might want to make an additional modification to clear the area on sheet "model" that contains the pivot table before creating the new one. If you don't, and the sheet changes size, you may see some unwanted data.
    Attached Files Attached Files
    Legare Coleman

  8. #8
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Hi Legare,
    Thank you very much, this is exactly what I wanted.

    Cheers

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Rene,

    Sorry for misunderstanding your initial question. However I see you got sorted out.

    I think it will be necessary to remove the existing Pivot Table before you can add another, unless you choose a different destination. Just as a matter of interest, would a simple Refresh not suffice, rather than re-creating the Pivot table each time.

    I have attached a sheet based on your own, but using the refesh method. I also make the assumption that no data other than your database exists on sheet 1 (which I have named Database). The Database range is automatically given the name "ModelList", and the pivot table uses that named range as it's source of data. If the list grows, that is catered for so the information in the pivot table is the latest. I also named the PivotTable "ModelPivot". The following macro refreshes the PivotTable rather than creating a new one. <pre>Sub UpdatePivot()
    Sheets("Database").UsedRange.Name = "ModelList"
    Sheets("Model").Select
    Sheets("Model").PivotTables("ModelPivot").RefreshT able
    End Sub</pre>


    Hope that is useful

    Andrew
    Attached Files Attached Files

  10. #10
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables

    Hi Andrew,
    Thanks a lot, I think this is an excellent way of doing what I want.

Posting Permissions

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