Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations John Tel on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Set cell value as variable

Status
Not open for further replies.

owentmoore

Technical User
Jul 20, 2006
60
IE
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
 
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
 



Hi,

Reference each range relative to an explicit worksheet...
Code:
Public MyWeekNum as Variant
Sub WeekNum()
    Sheets("WhatName").PivotTables("PivotTable4").PivotFields("Owner").CurrentPage = _
        Sheets("AnotherSheet?").cells(2,3).value
End Sub


Skip,

[glasses] [red][/red]
[tongue]
 
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
 


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

Skip,

[glasses] [red][/red]
[tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top