Changing Worksheet Name to apply formulas based on cell reference entry

[jaspalsd]

Hi,

I haven't touched VBA for a long time and hopefully someone can provide the simplest solution.

I have a dropdown list of worksheet names and whicever option is chosen, I wish to then have formulas applied to that workseet.

Can someone please advise on how to approach this. Thank you

Sub Commission_Percent()



Dim Lastrow As Long

Lastrow = Range("A" & Rows.Count).End(xlUp).Row


Dim strFormulas(1 To 4) As Variant




With Sheets("JBP") 'I want to dynnamically change this to what is entered in the Sheets("Commission").Range("C4").Select as there is a dropdown menu to select diff names
      
      
      strFormulas(1) = "=SUM(C2:E2)"
      strFormulas(2) = "=XLOOKUP(A2,Lists!C:C,Lists!B:B,0,0)"
      strFormulas(3) = "=J2*I2"
      
     
      
       .Range("I2:K" & Lastrow).Formula = strFormulas
    
      
   End With
   
   
   
With Worksheets("JBP")
    For i = 2 To Lastrow
        If .Range("J" & i).Value < .Range("B" & i).Value Then
            .Range("J" & i).Font.Color = vbRed
        Else
            .Range("J" & i).Font.Color = vbBlack
        End If
    Next i
End With



End Sub

 

[Joe4]

You should be able to just substitute:

Sheets("Commission").Range("C4").Value

for all your references of

"JBP"

If you want to capture in it in a variable for easier referencing, you could so soemthing like:

Dim ws as String
ws = Sheets("Commission").Range("C4").Value

The you can replace all references of:

Sheets("JBP")

with

Sheets(ws)

 

[jaspalsd]

You should be able to just substitute:

Sheets("Commission").Range("C4").Value

for all your references of

"JBP"

If you want to capture in it in a variable for easier referencing, you could so soemthing like:

Dim ws as String
ws = Sheets("Commission").Range("C4").Value

The you can replace all references of:

Sheets("JBP")

with

Sheets(ws)

thank you, you are a superstar, as I may have multiple worksheets, the last bit of code does the job nicely

 

[Joe4]

You are welcome!
Glad I was able to help!

 

[Joe4]

One other option, is to use/set a Worksheet object, which makes referencing it even easier, which may be preferrable if you are referencing this worksheet a lot.

You would first create the Worksheet object like this:

    Dim ws As Worksheet
    Set ws = Sheets(Sheets("Commission").Range("C4").Value)

Then, you could replace all references of this:

With Sheets(ws)

with this:

With ws