Amend VBA code to refer to another sheet

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Hi,

I have the below code which works fine if the target "No._Partners" is on the same sheet. However, in this case, "No._Partners" is based on another sheet. Can someone please help me change the first part of the code to refer to another sheet? I'm not quite sure how to do that..

Thanks!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("No._Partners")) Is Nothing Then


If Target.Cells.CountLarge > 1 Then Exit Sub
Select Case Target.Value


    Case "Please Select"
        Range("159:167").EntireRow.Hidden = True
    Case 2
        Range("159:167").EntireRow.Hidden = False
        Range("160:167").EntireRow.Hidden = True
    Case 3
        Range("159:167").EntireRow.Hidden = False
        Range("161:167").EntireRow.Hidden = True
    Case 4
        Range("159:167").EntireRow.Hidden = False
        Range("162:167").EntireRow.Hidden = True
    Case 5
        Range("159:167").EntireRow.Hidden = False
        Range("163:167").EntireRow.Hidden = True
    Case 6
        Range("159:167").EntireRow.Hidden = False
        Range("164:167").EntireRow.Hidden = True
    Case 7
        Range("159:167").EntireRow.Hidden = False
        Range("165:167").EntireRow.Hidden = True
    Case 8
        Range("159:167").EntireRow.Hidden = False
        Range("166:167").EntireRow.Hidden = True
    Case 9
        Range("159:167").EntireRow.Hidden = False
        Range("167:167").EntireRow.Hidden = True
    Case 10
        Range("159:167").EntireRow.Hidden = False
     
   End Select
End If
End Sub
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,108
Hello,
I am not sure if I understand you correctly.. but if you like to refer to another worksheet within the workbook you need a different event not the change event of the worksheet.
Create a sub instead of using the ChangeEvent of the worksheet and then you refer to another worksheet in that code.

HTH
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
365
Thank you. However, I'm not sure how I create a sub instead of using the ChangeEvent. Any chance you could elaborate please?
 

Forum statistics

Threads
1,089,330
Messages
5,407,618
Members
403,155
Latest member
ValenBaez

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top