• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Select Cell not working in a function

R

rjacmuto32

Member
I have this simple code below. It's using the worksheet change function and everytime the value in D2 is changed, then the pivot table gets updated and then I want to select a cell on another sheet. The code works great in the begining but the error is when I try to select Sheet2 then Range B1, i get a error o Debug. It can't select Cell B2. Anyone know why this is happening or how to fix it. It's a simple code.


Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$D$2" Then


Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh


End If


Sheets("Sheet2").Select

Range("B1").Select


End Sub
 
Hi ,


The problem is that the procedure , which is a Worksheet_Change procedure is in the worksheet where you are wanting the change to be recognized. Assuming that this is a sheet labelled SheetX , when you change to Sheet2 using the statement :


Sheets("Sheet2").Select


the statement Range("B1").Select is unqualified. If you use a statement :


Activesheet.Range("B1").Select


you will not get an error.


Narayan
 
Thanks. That worked great. I just started using the Worksheet Change Function in my files. Good to know about ActiveSheet.Range for those functions.
 
You must log in or register to reply here.
Back
Top