Activating a Sheet does not actually change the Active Sheet

[Ashaman99]

Hello,

I am trying to choose a sheet to read a cell(not the sheet that my macro button is on), then switch to a specific sheet based on that cell and then copy cells from that specific sheet to a third sheet in my workbook. The trouble I am having is that I cannot get the proper sheet to activate to read the data cell. It seems that the Active Sheet is "stuck" and all of these options below do not affect the ActiveSheet.Name value I see when I add that as a Watch.

Sheets(strDataSheet).Select Replace:=True
Sheet16.Select Replace:=False
Sheet16.Activate
ActiveWorkbook.Worksheets(strDataSheet).Select
Sheet16.Range("A14").Select

Any of these should have set the ActiveSheet to "Data", but it stays on the sheet the button is on. Other Scripts in this workbook will switch between sheets properly, so I do not understand why this particular one is so grumpy.

(Sorry the Script is so cluttered, I was trying many options and have not cleaned up what is not needed, as I do not yet know what I need)

The attachment below shows the Watch items and the ActiveSheet.Name with the value of a different sheet then the above commands should have changed.



This is Module 3 and is tied to the button on the "DashBoard" sheet around A62.

 

[Nebu]

Hi:
I was perusing your code in module 3 I could not make out .
If Solution = "y" Or Solution = "Y" Then you have commented the code
'Solution = ActiveCell.Value , hence the value of solution will be always empty . What are you trying to achieve here can you clean up the code and keep only the ones you need .

 

[Marc L]

Hi !​

The trouble I am having is that I cannot get the proper sheet to activate to read the data cell.

Just notice that you do not need to activate a worksheet to read a cell !
And a good code does not use Activate neither Select to read, copy cells …

As explained in VBA inner help, you just have to respect Excel levels :
application / workbook / worksheet / cell / method or property …
​

 

[NARAYANK991]

Hi ,

As already posted by others , there are a lot of ways in which your code can be streamlined ; however , I will try to address your doubts.

The following line of code :

ActiveWorkbook.Worksheets(strDataSheet).Select

will make the tab named Data the active sheet.

The next line :

Sheet16.Range("A14").Select

will make the cell A14 the active cell.

If we look at the code which you have posted , some of the posted lines have been commented out in your uploaded file , while one line is different. The actual lines , from your file , are as follows :

Sheets(strDataSheet).Select Replace:=True
Sheet16.Select Replace:=False
Sheet15.Activate
ActiveWorkbook.Worksheets(strDataSheet).Select
Sheet16.Range("A14").Select

The third line above is different from what you have posted.

However , the functioning of these lines will be as follows :

1. The tab named Data is activated , which means this sheet becomes the active sheet.

2. This line is redundant , since it references the same object as the earlier line ; hence the Replace parameter does nothing.

3. The tab named Error is activated , which means this sheet becomes the active sheet.

4. The tab named Data is selected , which means this sheet becomes the active sheet.

5. The cell A14 is made the active cell.

You can see that lines 2 , 3 and 4 together do practically nothing , though individually they do do something.

Go through the following link for more information :

http://dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/

Narayan

 

[Ashaman99]

Narayan and others,

Thank you for your comments. I see that I have confused everyone with my posting. Allow me to attempt to clarify:

I need to read a specific cell on a specific sheet(Data!A14), and compare that value to a list of possible values to determine the sheet I will need to read from in the next part of the script. To do this , I was attempting to select that cell on that sheet to make it the Active Cell so I could do my comparison.

ActiveWorkbook.Worksheets(strDataSheet).Select
Range("A14").Select
Month = ActiveCell.Value
If Month = "1" Then
NewMonth = "Jan"

I showed 5 different ways that I attempted to select that cell, all unsuccessful(While I agree with your summary of what these lines SHOULD do, if you run them in the script you will see that they do NOT actually select the sheet, which was my original problem in the post).

Based on the posts, it sounds like I should be able to just read the value in
Data!A14 and run my comparison from there. I am not being successful in finding the proper syntax for that. I tried variations of this:

Month = [Data!A14]
Month = (Data!A14)
Month = ("Data!A14")

But those either result in a syntax error or they do not load the value of that cell into the variable. So, If I could get some assistance with the proper syntax for this, that would solve my problem and eliminate the need to select the sheet, for now.

 

[Ashaman99]

Marc L,

Thank you for your input. Unfortunately, as my level of knowledge is limited, your post only serves to inform me that my code is wrong.

 

[NARAYANK991]

Hi ,

You assume that I posted what the lines should do.

I have stepped through the code line by line and posted what the code does.

Narayan

 

[NARAYANK991]

Hi ,

If you are having multiple workbooks open , then you should use ThisWorkbook instead of ActiveWorkbook.

Narayan

 

[Ashaman99]

Narayan,

That is very interesting. It does not function properly when I run it. The ActiveSheet.Name does not change as I step through those commands. I will run it on a different workstation and see if it works there.
Do you agree with Marc L that I should not need to Activate and Select the sheets, or am I generally headed in the right direction?

 

[NARAYANK991]

Hi ,

What Marc has mentioned is the proper programming practice , but that does not mean that code which has unnecessary Activates and Selects will not work !

Your first concern should be whether the code you have written does what you want it to ; efficiency , which is what Marc has highlighted may or may not be relevant to you or for your purposes.

Narayan

 

[NARAYANK991]

Hi ,

The syntax you have posted regarding assigning a value to Month is giving you an error because Month is a VBA keyword. As far as possible never declare or use variables which are also VBA keywords.

Month1 = [Data!A14]

does what you want it to.

Narayan

 

[Ashaman99]

Narayan,

Thank you for your assistance. A reboot addressed my ActiveSheet Selection issue. (Thanks, Windows, for making me crazy!)
I will change the Month variable to something else and continue on from here.