fbpx
Search
Close this search box.

Tables, PivotTables, and Macros: music to your ears

Share

Facebook
Twitter
LinkedIn

Howdy folks. Jeff Weir here again. You may remember me from posts such as What would James Bond have in his Personal Macro Workbook and my now infamous music review. Today – and this truly will be music to some ears – we’re going to concentrate more on the former and less on the latter.

Today we’re going to talk about that mystical place where hard tasks just disappear into thin air. Where is that place, I hear you ask? (I have supernatural powers). In that famed triangle of folk-law, of course:
Chandoo_Tables, PivotTables, and Macros_Bermuda

No, not that one. Stop jumping to conclusions and pay attention, will you! This one:

Chandoo_Tables, PivotTables, and Macros_triangle

Suddenly not quite as intrigued? Well, sure…if you add these three things together, no compasses go haywire, no spooky fog will obscures all physical features, and no planes, ships, or movie budgets will go missing. But plenty of tedious mind-numbing pivot-table formatting will disappear. Because combining these three things together in the right way could quite possibly remove ship-loads of needless clicking from your day. Let me explain.

Turn the Tables on Excel

The problem with Excel is that it is so damn high-maintenance: if you add new data to a spreadsheet, you might have to adjust the references in dozens of formulas and charts that point to the original data, so that the new items show up in your calculations and charts.

That’s where Tables come in. Excel Tables – known as ListObjects to VBA developers – were introduced in Excel 2007, and if you’re not familiar with them then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel.

A large part of their appeal is that they spookily expand to accommodate anything you put in them. Even better, anything pointed at that table – Formulas, Charts, Data Validation lists – gets automatically updated at the same time. Here, let’s look at an image, shall we?

Here’s a table that also has a formula, some Data Validation, and a Chart pointed at it. As you can see, whatever is in that table shows up in that formula, validation, and chart too.

Chandoo_Tables, PivotTables, and Macros_Before

I’ve put a red box underneath the table above, to highlight where we’re shortly going to add some new data. At the moment, the table above has got our weekly diet plan in it: Vege, Fruit, and Meat. Hardly a balanced diet. Watch what happens when we add something new under the table where that red box was…because man cannot live on fruit, veges, and meat alone. Well, not this man anyhow.
Chandoo_Tables, PivotTables, and Macros_New Item

Wow, will you take a look at that: the table expanded automatically to hold our new category of ‘Beer’ (just like my stomach does). And wow…those three things we had pointed at that table all got updated automatically, before we could say ‘Prost’. Spooky!

So how do Tables help with PivotTables?

First, let’s look at life without tables. Let’s say we make a PivotTable out of this ‘traditional’ block of data, and we make it display Total Sales by Region:

Chandoo_Tables, PivotTables, and Macros_CreatePivot

*BING!*

Chandoo_Tables, PivotTables, and Macros_PivotTable

Later on we scroll to the bottom and add a new record for a whole new region:

Chandoo_Tables, PivotTables, and Macros_NewData

…and then we refresh our Pivot:

Chandoo_Tables, PivotTables, and Macros_PivotTable Refresh

*BING!*

Chandoo_Tables, PivotTables, and Macros_Hey no data 2

Ahh, that’s right…when we initially set up our PivotTable, that Create PivotTable dialog box had a hard-coded range in it:

Chandoo_Tables, PivotTables, and Macros_HardCoded Range

…which means we need to click this puppy:

Chandoo_Tables, PivotTables, and Macros_Change Data Source

…and then change the hard-coded reference accordingly so that it includes the new data:

Chandoo_Tables, PivotTables, and Macros_What a drag 2

…and we need to do that each and every time we add new data. Maybe monthly. Maybe weekly. Maybe daily. Maybe for multiple pivot tables. Tedious.
 
 

Take two, with Tables

This time, we’ll turn our source data into an Excel Table first. There’s a couple of icons in the ribbon you can click to create a table – and bizarrely those icons are different – but I like to use the keyboard shortcut of Ctrl + T, which is easy to remember, as T stands for Terrific Table.

Chandoo_Tables, PivotTables, and Macros_Create Table

*BING!*

Chandoo_Tables, PivotTables, and Macros_Table

And now let’s create a PivotTable out of it:

Chandoo_Tables, PivotTables, and Macros_Create Pivot from Table

*BING!*

Chandoo_Tables, PivotTables, and Macros_PivotTable

Now watch what happens when we scroll to the bottom and add the new date for our new record:

Chandoo_Tables, PivotTables, and Macros_Table New Data 2

Well that in itself is pretty nifty. Yep, folks…tables have some smart functionality that in themselves can save you significant faffing around. Now let’s put in the rest of the data for that new record:

Chandoo_Tables, PivotTables, and Macros_Table completed

 
And here’s the punch-line: look what happens when you refresh that Pivot:

*BING!*

Chandoo_Tables, PivotTables, and Macros_Table Refresh3

…and I can tell from here just how excited you are by that from the look on your face (you left your web-cam on again), because…

Chandoo_Tables, PivotTables, and Macros_Never click again

Let’s throw some Macros into the mix

I promised you I was going to save you a ship-load of clicks. So far I’ve saved you…let me see…exactly one. What about them others I promised?

Well, given we’ve just established that Pivots love Tables more than I love this album, let’s whip up a macro that will not only create a PivotTable but also automatically turn the source data range into an Excel Table. Then we can assign it to a handy shortcut key – something like Ctrl + Shift + P (“P” for Pivots…I know what you’re memory is like) – so that all you have to do is select a cell in your raw data and in one keyboard shortcut do two things that otherwise would require several clicks. Now that would be worth reading this far, wouldn’t it!

And while we’re at it, let’s code it up so that if you run it on an existing PivotTable, it will retrospectively turn the source data into a Table if needed, and then re-point the Pivot at that Table. That would be handy too.

But why stop there? How ’bout we get it to do a whole bunch of other tiresome things that we routinely do manually in order to set our Pivots up just the way we like ’em. Because if there’s one thing I just can’t stand about pivots, it’s the huge number of things I have to do every single time when I whip one up in order to get it looking just how I like it.

So – as Prince once said, “Let’s go crazy“:

  • Let’s make it put the PivotTable that we just created at the edge of the used part of the sheet that we’re working in – which is usually right by our data, and usually exactly where I actually want it – rather than having to uncheck that pesky “In New Worksheet” button and then having to manually select the range where I want my new pivot to go;
  • Let’s have it cut that PivotTable with a Ctrl + C, so that if we choose to, we can then navigate to any cell we want and simply hit Ctrl + V to paste it into it’s new home. (And if we choose not to move it, we simply do nothing, because it actually stays where it is unless we actually paste it somewhere else;
  • Let’s change the Report Layout to “Show in Tabular Form” instead of the default “Show in Compact Form” setting that I never use;
  • Let’s turn on the “Repeat All Item Labels” option that I practically always want;
  • Let’s turn off Subtotals, because I almost never need them on any field, let alone all of them;
  • Let’s turn off Grand Totals, because those totals don’t always make sense in the context of my Pivots, and they’re simple enough to turn back on if I do need them;
  • Let’s turn off that really annoying “Autofit Column Width on Update” setting, so that my pivot doesn’t stupidly screw up all my carefully set up column widths each time I refresh it;
  • Let’s turn off the “Save Source Data with file” option. No point saving the PivotCache along with the source data, given it only takes an instant to recreate the PivotCache from scratch in the event that we need to. (More on this here).

Wait a minute Jeff…you missed a really annoying thing…

Oh yeah, so I did. Let’s make the Pivot automatically adopt the same source formatting as the original data has – like Mike does over at the Bacon Bits blog – because if there’s one thing guaranteed to make an Excel user do this:

Chandoo_Tables, PivotTables, and Macros_Ahhhhrgh

…it’s either an off-topic post, or (more likely) this:

Chandoo_Tables, PivotTables, and Macros_Does not match
Chandoo_Tables, PivotTables, and Macros_I hate it 4

Here’s the code that will free you from this Pivot Hell:

Just cut the below code, and paste it into your Personal Macro Workbook. Don’t know what that means? Think I’m speaking gibberish? Head over to my earlier post What would James Bond have in his Personal Macro Workbook to find out just how easy this is, and you’ll be a ninja plus in no time!



Sub InstantPivot()

'   InstantPivot: Just Add Water
'   Assign this to Ctrl + Shift + P or something like that.

'   Description:    * Turns selection into Excel ListObject
'                   * Makes a Pivottable out of it at the edge of the used range
'                   * Applies my preferred default settings
'                   * Selects the Pivot and cuts it, so that
'                     you can then use arrow keys
'                     and Control + V to paste it where you wants
'                     without having to touch that unclean dusty rodent
'                     you keeps at the edge of your Desk.Usedrange
'


'Here's the settings it applies.
'   1.  Changes the Report Layout to "Show in Tabular Form"
'   2.  Turns on  "Repeat All Item Labels" option
'   3.  Turn off Subtotals
'   4.  Turn off Grand Totals
'   5.  De-selects the Row Headers option from the Design tab.
'   6.  Turns off 'Autofit Column Width on Update'
'   7.  Turns off 'Save Source Data with file' option.
'   6.  Adopts the source formatting


'   Programmer:     Jeff Weir
'   Contact:        weir.jeff@gmail.com or jeff.weir@HeavyDutyDecisions.co.nz

'   Name/Version:   Date:       Ini:   Modification:
'   InstantPivot    20140213    JSW     Initial programming
'   InstantPivotV2  20140216    JSW     Added error handler and check for multiple cells
'   InstantPivotV3  20140216    JSW     Adopted SNB's approach of setting numberformat while turning subtotals off
'   InstantPivotV4  20140216    JSW     If run on existing pivot that is not based on ListObject, turns source into ListObject
'   InstantPivotV5  20140216    JSW     Now ignores Values fields and doesn't apply format if pf.function = xlCount
'   InstantPivotV7  20140216    JSW     Now ignores Values fields and doesn't apply format if pf.function = xlCount

'   Inputs:         None

'   Outputs:        PivotTable is formatted accordingly. World recognizes my genius and forgives me my occasional off-topic post.

    Dim pc As PivotCache
    Dim pf As PivotField
    Dim pt As PivotTable
    Dim lo As ListObject
    Dim rng As Range
    Dim strLabel As String
    Dim strFormat As String
    Dim i As Long
    Dim wksSource As Worksheet

    
    'Check that we're dealing with a version of Excel that supports ListObjects
    ' In fact, play it safe, and ignore Excel 2007.
    If Application.Version >= 14 Then
    

        On Error Resume Next
        Set pt = ActiveCell.PivotTable
        On Error GoTo errhandler
        If pt Is Nothing Then
            Set lo = ActiveCell.ListObject
            If lo Is Nothing Then Set lo = ActiveSheet.ListObjects.Add(xlSrcRange, Selection.CurrentRegion, , xlYes)
            Set rng = Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column + 1)
            Set pc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=lo)
            Set pt = pc.CreatePivotTable(TableDestination:=rng)
        Else:
            'Check if pt is based on a ListObject.
            '  *  If so, set lo equal to that ListObject
            '  *  If not, turn that source data into a ListObject
            On Error Resume Next
            Set lo = Range(pt.SourceData).ListObject
            On Error GoTo errhandler
            If lo Is Nothing Then
                Set rng = Application.Evaluate(Application.ConvertFormula(pt.SourceData, xlR1C1, xlA1))
                Set wksSource = rng.Parent
                Set lo = wksSource.ListObjects.Add(xlSrcRange, rng, , xlYes)
                pt.ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=lo.Name)
            End If
    
        End If
    
        With pt
            .ColumnGrand = False
            .RowGrand = False
            .RowAxisLayout xlTabularRow
            .RepeatAllLabels xlRepeatLabels
            .ShowTableStyleRowHeaders = False
            .ShowDrillIndicators = False
            .HasAutoFormat = False
            .SaveData = False
            .ManualUpdate = True
            If ActiveCell.CurrentRegion.Cells.Count > 1 Then
                For i = 1 To .PivotFields.Count - .DataFields.Count 'The .DataField.Count bit is just in case the pivot already exists
                    Set pf = .PivotFields(i)
                    With pf
                        If pf.Name <> "Values" Then
                            .Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
                            On Error Resume Next
                            .NumberFormat = lo.DataBodyRange.Cells(1, i).NumberFormat
                            On Error GoTo errhandler
                        End If
                    End With
                Next i
            End If
        End With
        
        ' Get DataFields to match the formatting of the source field
        ' Note that this will only be neccessariy in the case that we're
        ' running this code on an existing pivot
        On Error GoTo errhandler
        If pt.DataFields.Count > 0 Then
            For Each pf In pt.DataFields
                If pf.Function <> xlCount Then pf.NumberFormat = pt.PivotFields(pf.SourceName).NumberFormat
                ' Do away with 'Sum of' or 'Count of' prefix etc if possible
                On Error Resume Next
                pf.Caption = pf.SourceName & " "
                On Error GoTo errhandler
            Next pf
        End If
    
        'This needs to go before the .Cut bit, otherwise the .Cut stack gets wiped
         With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = xlAutomatic
        End With
          
        With pt
            .ManualUpdate = False
            .TableRange2.Select
            .TableRange2.Cut
        End With
    Err.Clear
errhandler:
            If Err.Number > 0 Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                    .Calculation = xlAutomatic
                End With
                MsgBox "Whoops, there was an error: Error#" & Err.Number & vbCrLf & Err.Description _
                         , vbCritical, "Error", Err.HelpFile, Err.HelpContext
            End If
    End If
    
End Sub

What will you do with all your new spare time?

I’m glad you asked. Why, you’ll have PLENTY of extra free time now in which to give my new favorite album a listen:
Chandoo_Why you should close Excel_album

Chandoo_Tables, PivotTables, and Macros_nooooo3

TRANSMISSION ENDS

Chandoo_Why you should close Excel_test pattern

—Redux—
It’s just gone 22 minutes past Midnight here in New Zealand, and I’ve just got back from Tami Neilson’s album release party for her album Dynamite. It certainly was. I’d say sparks were flying off guitarist and co-producer Delaney Davidson’ guitar but that would be poor poetic license. Because in actual fact, blazing chunks of molten steel were flying off of that beast’s bridge.

I’d say that Tami was twice the woman live than she is recorded. And that’s not too far from the truth, because she is 6 months pregnant, and counting. Not that that was any hindrance whatsoever to her belting out an incredible lyric. That baby of hers is going to have one hell of a set of lungs, if genetic predisposition is anything to go by.

And if we focus on the nurture side of the nurture/nature argument, then that baby is going to have one hell-of-a sense of rhythm too, because it had front-row seats to the craftiest drummer I have ever heard. Why at one stage that drummer threw down his sticks and wrestled beats out of that kit with his bare hands like he would wrestle a live bear. And the bear definately came off second best.

And then there’s the bass-player. Not only was he a damn fine singer, but he also had the longest g-string on stage by far. (Explanation: one of the strings on a Bass guitar is tuned to ‘G’, as is one of the strings on a Guitar. And because a Bass Guitar has a longer neck than an electric rhythm/lead guitar, that G-string is longer. I know, it’s a bad joke.) Eligible Bachelor Number Two was his name. Fastest fingers in the west.

Don’t even get me started on Eligible Bachelor Number Three, the rhythm acoustic and fiddle player. Ye-haw and yes-siree does not even begin to cut it as a compliment to this dude. If I still had a soul and a willing buyer for it, I’d only end up with half the riffs this guy can pull off in exchange for it.

What a night.

🙂

Here’s what you missed:

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

35 Responses to “Tables, PivotTables, and Macros: music to your ears”

  1. Hui... says:

    Jeff
    So disappointed that your last line wasn't "Oh, What a night"
    With the following hyperlink http://www.youtube.com/watch?v=wD7LNDLKfWY&feature=kp

  2. Jennifer says:

    Great post. I laughed, I learned. What else could I ask for on a Friday morning.

    • Jeff Weir says:

      What else could I ask for on a Friday morning.

      A server outage. Followed by a phonecall from the boss saying "Don't bother coming in today...the computers aren't going. Have a day off on us",

      🙂

  3. Andy Spragg says:

    OK folks, once is unlucky, twice is just careless. Jeff, I don't know what you put in your contributions that breaks the email versions of your posts, but you did it again. I clicked on the latest chandoo email just now, and once again my email app (Groupwise) was a goner. Just not responding. Really difficult to kill it and start over, and I thought for a while it was going to take everything else down with it and require forcing a power off, but fortunately not; once I managed to close Groupwise, other apps came back to life. So I unsubscribed from the email list 🙁

    • Jeff Weir says:

      Maybe it's something to do with the embebbed YouTube videos? That's what my two most recent posts have in common. But I don't follow how that makes me 'careless'? Sounds like you're holding me personally responsible for some Groupwise thing.

    • Sally says:

      I"ve had the same thing happen in the last two emails.
      But when I check some of the older emails I have saved, I'm getting the same thing with them.
      Those older emails opened up perfectly fine when the came in, not they are causing a not responding, and even using ctrl,alt,del it takes ages and ages to shut them down.
      Hope someone has a fix for this
      🙂

  4. Michael (Micky) Avidan says:

    Hi,
    As far as I recall - since "Excel 2007" was launched, - if you transform the Pivot Table's source range from 'cells range' into 'Table' - there is NO NEED to re-adjust the PivotTable's source range, because it is re-adjusting automatically after the PT has been 'Refreshed' (and, yes, it is a great PT disadvantage that one must run a refresh after every change in the data source (unless you use a Worksheet_Change n Event-Macro in order to refresh the PT).
    Michael (Micky) Avidan

    • Jeff Weir says:

      As far as I recall – since “Excel 2007? was launched, – if you transform the Pivot Table’s source range from ‘cells range’ into ‘Table’ – there is NO NEED to re-adjust the PivotTable’s source range

      Yes Micky, that is exactly what my post says.

      • Michael (Micky) Avidan says:

        Well...., Jeff,
        Although English was not my mothers tongue - I reffered to what you wrote [quote]:
        "Ahh, that’s right…when we initially set up our PivotTable, that Create PivotTable dialog box had a hard-coded range in it:
        …which means we need to click this puppy:
        …and then change the hard-coded reference accordingly so that it includes the new data:
        …and we need to do that each and every time we add new data. Maybe monthly. Maybe weekly. Maybe daily. Maybe for multiple pivot tables. Tedious." [end of quote]
        So, as far as I understood this mean that the user must re-adjust the PT range whenever a record/row is added ? and I'm talking on a situation BEFORE converting the source range into "Table' ?
        If you would have emphasized that you refer to "Excel 2000-2003" then you were right - but in my comment I reffered to 2007 and above which are nowadays the most popular versions.
        Did I made myself clear or am I missing something ?
        Thanks, Micky

        • Jeff Weir says:

          So, as far as I understood this mean that the user must re-adjust the PT range whenever a record/row is added ? and I’m talking on a situation BEFORE converting the source range into “Table’ ?
          Correct.

          If you would have emphasized that you refer to “Excel 2000-2003? then you were right – but in my comment I reffered to 2007 and above which are nowadays the most popular versions.

          My comment applies to any version. I'm saying that:

          • If you create a PivotTable from a non-table range, then if you add more data to that range, you've got to use that 'Change Data Source' button
          • If you happen to have Excel 2007 or later, then if you change that range into a table before you create the PivotTable, then you don't have to use that 'Change Data Source' button ever again
          • Michael (Micky) Avidan says:

            That U R Applying 2 all kind of version was understood (by me) right from the beginning.
            What was (and still) missing, within the content(!) the tip(!), is the second halt of your comment:
            If you happen to have Excel 2007 or later, then if you change that range into a table before you create the PivotTable, then you don’t have to use that ‘Change Data Source’ button ever again.
            "within the content(!) the tip(!)" becomes vital because not everyone reads all the following comments especially when they get 2 B as long as volume #1 of "War and Peace" by tolstoy. 😉
            Have a great weekend,
            Micky

          • Jeff Weir says:

            Okay, I think you're talking about the fact that I forgot to mention that tables are only available in Excel 2007 or later?

            If so, have amended the article to include this:
            Excel Tables – known as ListObjects to VBA developers – were introduced in Excel 2007, and if you’re not familiar with them then I strongly suggest you check out Chandoo’s Introduction to Structural References and this great video he did with MrExcel.

            Cheers.

            Jeff

    • Col Delane says:

      Without suggesting that this technique is superior to "formatting" your PT source data as a structured table, there is an alternative: create a Defined Name that refers to the source data range and link the PT to that name. However, rather than using fixed cell references in the RefersTo field of the Defined Name, use a combination of OFFSET and COUNTA (or COUNTA) functions to make the references 'dynamic' by adjusting the width (columns) and height (rows) expand/contract to match the size of the range. The RefersTo formula is like this:
      =OFFSET($A$1,0,0,COUNTA(A:A),COUNTA(1:1)) where A1 is the first field heading, A is a column in the source data that will always have a string value for every record (doesn't need to be first column), and row 1 holds the field headings.

  5. Michael (Micky) Avidan says:

    ...and by the way:
    During the 1990's, 5 string Bass guitars became widely used by professional bassists.
    The fifth string is a low "B".
    Michael (Micky) Avidan

    • Jeff Weir says:

      Yeah, that's why I said 'usually'

      • Michael (Micky) Avidan says:

        Doesn't 'usually' means most of the time ?
        If so - then the word 'rarely' is more appropriate...

        • Jeff Weir says:

          Haved changed my somewhat labored joke to this:
          And then there's the bass-player. Not only was he a damn fine singer, but he also had the longest g-string on stage by far. (Explanation: one of the strings on a Bass guitar is tuned to 'G', as is one of the strings on a Guitar. And because a Bass Guitar has a longer neck than an electric rhythm/lead guitar, that G-string is longer. I know, it's a bad joke.)

          It might be a lame joke, but it makes an excellent bumper sticker:
          Bass players have longer G-strings

          🙂

  6. json says:

    Hay Jeff!!

    Long time listener, first time caller... hahhaah

    1) you're posts are insightful and crazy entertaining

    2) have you had your meds lately!?

    HAHAHAHHA

  7. Ken Puls says:

    Nice job, Jeff! I don't think I've ever read an Excel post that made me laugh that hard. For my cultural education though, I'm hoping that you might be able to roll a bit more hints to listen to country music in to the next couple of posts. I'm not quite convinced yet. 😉

    • Jeff Weir says:

      Hi Ken. I added a video of Tami's latest song to the bottom of the post. Check it out. Not country. Great song. Great vid too. There's other songs on the album in the same style. Especially the title track "Dynamite"

      Give it a listen, and post back on your thoughts...good, bad, or indifferent.

  8. Mark Salisbury says:

    Laughed my socks off, but more importantly, learnt a massive amount. Given me a whole new perspective on the development of dynamic reports and charts.

    This is really the way applications should be taught. Love it, keep it up - just don't force me to listen to Country Music - PLEASE!

    • Jeff Weir says:

      Mark - that is the highest praise a man could get. Thank you most kindly. Check out the vid of Tami that I added to the original post...not country. Just awesome.

  9. Dave says:

    Jeff, awesome post as usual! Loved the infamous post as well - actually loved the comments mainly (well handled) and the references in your support elsewhere on the web! Great code here thank you, very helpful as is and great to review as a learning tool as well. Cheers from across the Tasman, I'll go now and have a Monteiths or something in your honour. 🙂

  10. Jacob says:

    Hey Jeff, really enjoyed the post. Will definitely be sticking this one in my personal workbook!
    Jacob

  11. Crystal says:

    Hey there. Not just groupwise, I got javascript errors and crashes on the email too.

    So since I'm using Excel 2007 on a locked down system that I don't have any upgrade privileges on, does that mean that the line
    'Check that we're dealing with a version of Excel that supports ListObjects
    ' In fact, play it safe, and ignore Excel 2007.
    If Application.Version >= 14 Then...

    Means I don't get any time to go listening to music???

    • Jeff Weir says:

      Hi Crystal. You could try changing that to If Application.Version >= 12 and see if Excel plays ball. Let me know if you get any errors and on what line they occur, and I'll take a stab at amending it accordingly. Unfortunately I don't have 2007 anymore to test.

  12. […] word about everlastin’ VBA serenity. You may remember me from mah preeeevious sermons such as Tables, PivotTables, and Macros: music to your ears and Big trouble in little […]

  13. Khalid NGO says:

    Hi Jeff Sir,
    Great post, I learned very new things and enjoyed your post.

    Thanks for sharing.
    Have a great weekend.

    Khalid

  14. Vishal says:

    Hi,

    I have a lot of pivots for which show the data for 5 weeks. Now every new week, I have to take the oldest week number out and select the new week in. Is there a VB script to do this?

  15. marionffavp says:

    Hi,

    I am trying this out with Excel 2013 and the table does not seem to want to add the new lines of data to the table. Also, the Pivot table that was created after the table data was entered does not update on it's own. They reside in separate workbooks if that makes a difference.

    Thanks

    • marionffavp says:

      I have checked for myself and it does not seem to work in Excel 2013 the way that was shown above. I could be missing a step or something....but I don't think so.

Leave a Reply