Can a variable be used in a Worksheet().Activate method

I am needing to place data into different worksheets depending on choices
from the user. I am needing to activate the worksheets based on those
choices. I was using the folowing code but am running into problems, and am
not sure how to get it to work.

'Day is row to place info in
'Movie is the name of the movie for that time
'PCount is the Performance Count for that time
Dim Day As Integer
Dim PCount As Integer
Dim Movie As Integer

Private Sub cmdApply_Click()
Range("A3").Select
Day = ActiveCell.Value + 3
'9am
Range("C5").Select
Movie = ActiveCell.Value
Range("C3").Select
PCount = ActiveCell.Value
Worksheets(Movie).Active
Range("C" & Day).Select
ActiveCell.Value = PCount

the movie variable has been done as a integer with the sheet index and as a
string with the sheet name both having problems. Any help is appreciated.

I have changed the way I need this to operate I need to clear out the values
on the same time and day on the other sheets so I changed it to look like
this, how can I get this to work?

For X = 1 To ActiveWorkbook.Sheets.Count
Worksheets(X).Active
Range("C" & Day).Select
If X = Movie Then
ActiveCell.Value = PCount
Else
ActiveCell.Value = 0
End If
Next X


The Worksheets(X).Active is where the error happens, how can I accomplish
this otherwise?

Instead of using active sheets and active cells how about using the actual
sheet code names. In the VB explorer you see a listing of all of the sheets,
forms and modules. Double clicking on a sheet or moduel takes you to the code
for that sheet or module. At this point ensure that the properties is visible
(View -> Properties). The first item in the list of prpoerties for a sheet is
(Name). This is the code name of the sheet and it will say something like
Sheet1. You can change it and then reference the sheet directly in code. As a
standard I always premise my sheet code names with sht so you could have
names like shtMovies and shtHome. Once this is done in the code window you
can have code like this...

Private Sub cmdApply_Click()
Day = shtHome.Range("A3").value + 3
'9am
Movie = shtHome.Range("C5").value
PCount = shtHome.Range("C3").value
shtMovies.Range("C" & Day).value = PCount


--
HTH...

Jim Thomlinson


"Brad Sumner" wrote:

> I am needing to place data into different worksheets depending on choices
> from the user. I am needing to activate the worksheets based on those
> choices. I was using the folowing code but am running into problems, and am
> not sure how to get it to work.
>
> 'Day is row to place info in
> 'Movie is the name of the movie for that time
> 'PCount is the Performance Count for that time
> Dim Day As Integer
> Dim PCount As Integer
> Dim Movie As Integer
>
> Private Sub cmdApply_Click()
> Range("A3").Select
> Day = ActiveCell.Value + 3
> '9am
> Range("C5").Select
> Movie = ActiveCell.Value
> Range("C3").Select
> PCount = ActiveCell.Value
> Worksheets(Movie).Active
> Range("C" & Day).Select
> ActiveCell.Value = PCount
>
> the movie variable has been done as a integer with the sheet index and as a
> string with the sheet name both having problems. Any help is appreciated.

Try this...

dim wks as worksheet

for each wks in worksheets
if wks.name = "Movie" then
wks.Range("C" & Day) = PCount
else
wks.Range("C" & Day) = 0
end if
next wks
--
HTH...

Jim Thomlinson


"Brad Sumner" wrote:

> I have changed the way I need this to operate I need to clear out the values
> on the same time and day on the other sheets so I changed it to look like
> this, how can I get this to work?
>
> For X = 1 To ActiveWorkbook.Sheets.Count
> Worksheets(X).Active
> Range("C" & Day).Select
> If X = Movie Then
> ActiveCell.Value = PCount
> Else
> ActiveCell.Value = 0
> End If
> Next X
>
>
> The Worksheets(X).Active is where the error happens, how can I accomplish
> this otherwise?