Set cell value as variable

[owentmoore]

Hi all,

I need a pivot table to display data depending on the value in a cell.

I am using a variable as follows:


Public MyWeekNum as Variant

MyWeekNum = cells(2,3).value

Then through a macro I call the following:

Sub WeekNum()
ActiveSheet.PivotTables("PivotTable4").PivotFields("Owner").CurrentPage = _
WeekNum
End Sub

My two questions are:
1) Am I setting the value of the variable correctly?
2) Am I calling the variable correctly in the macro?
Thanks a lot
Owen

 

[PHV]

2) you call the variable as WeekNum instead of MyWeekNum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Reference each range relative to an explicit worksheet...

Public MyWeekNum as Variant
Sub WeekNum()
    Sheets("WhatName").PivotTables("PivotTable4").PivotFields("Owner").CurrentPage = _
        Sheets("AnotherSheet?").cells(2,3).value
End Sub

Skip,

_[red][/red]

 

[owentmoore]

Skip

Using your code, I wouldn't need to use the "Public MyWeekNum as Variant" line would I?

I could just use the rest by itself, i.e.

Sub WeekNum()
Sheets("WhatName").PivotTables("PivotTable4").PivotFields("Owner").CurrentPage = _
Sheets("AnotherSheet?").cells(2,3).value
End Sub

Correct?

Owen

 

[SkipVought]

You could if you need that value elsewhere in your program.

Skip,

_[red][/red]