my vba code isnt working

[bigdan]

I'm new to VBA so this is probably an elementary mistake. I created this code by recording my actions.

Essentially I want to be able to double-click on a cell in a pivot table to see what's going on there. I'll do that manually. That'll then open up a dataset which has 43 columns. The number of rows vary, they could be 3 they could be 7800.

So anyway when there's 43 columns I want to hide the irrelevant stuff. I generated this code to do just that. But it's not working when I doubleclick on a different cell in the pivot. I'm guessing it's objecting to the number of rows but am not sure. I'd assume the columns are the exact same.

The error is: Run-time error 1004. Select method of range class failed.


Here's the code.

Sub Grouping()
'
' Grouping Macro

Columns("A:E").Select
Selection.Columns.Group
Columns("G:I").Select
Selection.Columns.Group
Columns("K:O").Select
Selection.Columns.Group
Columns("Q:AC").Select
Selection.Columns.Group
Columns("AG:AQ").Select
Selection.Columns.Group
Range("Table1[[#Headers],[ACCOUNT_NO]]").Select
End Sub

 

[Micron]

Try referencing the sheet name, as in
Sheets("Sheet1").Range("Table1[[#Headers],[ACCOUNT_NO]]").Select

 

[bigdan]

I'm afraid that didn't work. I got the exact same error message. But I noticed that although I got an error, the grouping was done properly. And then realized the line that's giving a problem is at the end of the code. So I've just commented that out and yeah that seems to work!

That said, why is that line giving an objection?

 

[Micron]

Sorry, no idea other than to guess it doesn't like something about the syntax of the range reference.