+ Reply to Thread
Results 1 to 12 of 12

Macro to create a pivot with variable rows.

  1. 02-20-2013, 12:11 PM #1
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Macro to create a pivot with variable rows.

    Hi Guys

    I am attempting to create a Macro which will create a pivot. I intend to place a button on the screen so others can run it.

    I have researched the net and come up with the following solution 
    Please Login or Register  to view this content.
    FianlRow = Cells(Rows.Count, 1).End(xlUp).Row
    DataSheet = ActiveSheet.Name
    NewSheet = ActiveSheet.Name
    ActiveCell.Offset(39, 1).Range("A1").Select
    Sheets.Add
    NewSheet = ActiveSheet.Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    DataSheet & "!R2C1:R & FinalRow & C17", Version:=xlPivotTableVersion14). _
    CreatePivotTable TableDestination: NewSheet & "!R3C1", TableName:="PivotTable2",
    DefaultVersion:=xlPivotTableVersion14
    Sheets(NewSheet).Select
    Please Login or Register  to view this content.
    [/CODE]
    Then carries on with the actual manipulation within the pivot. This part does not work for me. I have not found a solution in this forum but I am sure that you will immediately see where the problem is. I got this from http://www.youtube.com/watch?v=UkPxhlW9Hd0

    Have uploaded the file.

    Any suggestions would be great.

    Thanks
    Mark
    Attached Files Attached Files
    Last edited by markDuffy; 02-22-2013 at 09:31 AM.
    Reply With QuoteRegister To Reply
  2. 02-20-2013, 12:24 PM #2
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to create a pivot with variable rows.

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  3. 02-20-2013, 02:05 PM #3
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Macro to create a pivot with variable rows.

    Thanks have doe that using Edit. just waiting now to see if its gone through.
    Reply With QuoteRegister To Reply
  4. 02-20-2013, 02:40 PM #4
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to create a pivot with variable rows.

    Try changing 
    Please Login or Register  to view this content.
    to 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-20-2013, 03:34 PM #5
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Macro to create a pivot with variable rows.

    Hi Administrator
    Just about to republish with that exact change. I had not copied it correctly form the video. But a line below and I am still getting a Compile Error
    Expected:expression. 
    Please Login or Register  to view this content.
    here it highlights the & after new sheet. Should i republish the updated excel?
    Thanks
    Mark
    Reply With QuoteRegister To Reply
  6. 02-21-2013, 02:50 AM #6
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Macro to create a pivot with variable rows.

    Change 
    Please Login or Register  to view this content.
    to 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 02-21-2013, 02:58 AM #7
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,358

    Re: Macro to create a pivot with variable rows.

    Your original code has a typo at the start:
    Please Login or Register  to view this content.
    should be
    Please Login or Register  to view this content.
    Option Explicit would have caught that! Also you should enclose the sheet name in single quotes.
    Reply With QuoteRegister To Reply
  8. 02-21-2013, 01:56 PM #8
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Macro to create a pivot with variable rows.

    Hi Guys

    Thanks again.

    Seriously embarrassed that on two occasions I am being corrected for what appears to be an inability to copy as all the above are in the example I was shown in the Video mentioned at the top. Not an excuse but I am dyslexic which partly means that I see things as I want to see them or that's my take. 69 can look like 96 all day long.

    I have gone over it again and again to see if there is a basic mistake. I can't see one if there is so I am uploading it. I know that it will eventually work as I have always succeeded her on this forum.

    All the best
    Mark
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 02-22-2013, 06:06 AM #9
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,358

    Re: Macro to create a pivot with variable rows.

    Try this:
    Please Login or Register  to view this content.
    Everyone who confuses correlation and causation ends up dead.
    Reply With QuoteRegister To Reply
  10. 02-22-2013, 09:31 AM #10
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Macro to create a pivot with variable rows.

    Hi Romperstomper

    It is absolutely beautiful. My next need was how to have the same do multiple pivots in the same workbook but this already does that. I did have one small issue where the sorting and ascending caused an error. So I have removed this and it works fantastic. I can handle that at the bottom.

    Removed from macro
    Please Login or Register  to view this content.
    I am now going to spend most of the weekend working out how it all works as your code is half the length of mine which I had made no attempt to shorten. But if you have time could you please heavily comment this. It will really help to understand it. But thanks again and again. It is fabulous.

    Education comes from the Latin and means to the lead through. Learning is so much quicker when we don’t have to keep reinventing the wheal.

    Have a great day

    Mark
    Reply With QuoteRegister To Reply
  11. 02-22-2013, 10:13 AM #11
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,358

    Re: Macro to create a pivot with variable rows.

    Revised version with comments and the sorting issue fixed:
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  12. 02-22-2013, 10:44 AM #12
    markDuffy
    markDuffy is offline
    Forum Contributor
    Join Date
    03-25-2011
    Location
    London, England
    MS-Off Ver
    office 2021
    Posts
    317

    Re: Macro to create a pivot with variable rows.

    Cheers it will still take the weekend to understand it but that will be a pleasure Thanks again
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1