[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 752
  • Last Modified:

vba error: argument not optional

Hi Experts!

New with excel vba. I need to change the filter of the pivot table by letting the user type the filter that he wants. The cell range name is "region" and i have a button. Whenever the button is clicked, the pivot table filter should change depending on what was typed by the user. I tried the code below but i had a vba error: argument not optional.

Am i missing something here?

Thanks!
Sub changeFilter(Range As String)

Dim rng As Range
rng = Range(region)

ActiveSheet.PivotTables("PivotTable2").PivotFields("region").CurrentPage = rng

End Sub

Open in new window

0
j2jake
Asked:
j2jake
  • 6
  • 5
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
This isn't causing the problem but you need a Set statement:

Sub changeFilter(Range As String)

Dim rng As Range
Set rng = Range(region)

ActiveSheet.PivotTables("PivotTable2").PivotFields("region").CurrentPage = rng

End Sub

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
How are you calling changeFilter? Can you post that code?

Kevin
0
 
j2jakeAuthor Commented:
Hi zorvek,

I'm pretty new with VBA. That is the only code that I have. How can I call it?

jake
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zorvek (Kevin Jones)ConsultantCommented:
When you click a button you cannot pass a parameter. You will have to deduce the "Range" from the environment. Can you tell me anything about how the parameter "Range" can be determined?

Kevin
0
 
j2jakeAuthor Commented:
Kevin,

The user types the region in one of the cells.

Does that help?
0
 
zorvek (Kevin Jones)ConsultantCommented:
What cell?

Kevin
0
 
j2jakeAuthor Commented:
It is cell F2.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Sub changeFilter()

ActiveSheet.PivotTables("PivotTable2").PivotFields("region").CurrentPage = ActiveSheet.[F2].Value

End Sub

Kevin
0
 
j2jakeAuthor Commented:
Hi Kevin,

unfortunately, it did not work. it gave me "400" error

i have uploaded a sample data. please check.

jake
sample.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
Hmmm...you do realize that when you create a new pivot table Excel creates a new name for it, right?

Change:

ActiveSheet.PivotTables("PivotTable2").PivotFields("region").CurrentPage = ActiveSheet.[F2].Value

to:

ActiveSheet.PivotTables("PivotTable3").PivotFields("region").CurrentPage = ActiveSheet.[F2].Value

;-)

Kevin
0
 
j2jakeAuthor Commented:
I think I got too excited. It worked! Thanks so much!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now