Assigning the worksheet value from a cell value

[grexcelman]

Can someone tell me the syntax for writing the following when I have the name of the sheet in a cell value?

The following works for me:

Dim ws as worksheet
 Set ws = ThisWorkbook.Worksheets("Systems")

I'd like to be able to change the name of the worksheet so I can apply the remaining code regardless of the sheet name, I have roughly 12 sheets with data with the name of each sheet in cell B1. I can't seem to get the value into the above function.

Any suggestions?

 

[Roderick_E]

ThisWorkbook.Sheets(1).Name = "TEST"

where (1) is the index "name" of the sheet

[B]ThisWorkbook.Sheets(1).Name = [B]ThisWorkbook.Sheets(1).[/B]range("B1")[/B]

 

[grexcelman]

ThisWorkbook.Sheets(1).Name = "TEST"

where (1) is the index "name" of the sheet

[B]ThisWorkbook.Sheets(1).Name = [B]ThisWorkbook.Sheets(1).[/B]range("B1")[/B]

Thanks for your quick reply. To be sure, would my syntax now read:

ThisWorkbook.Sheets(1).Name = ThisWorkbook.Sheets(1).Range("B1")
Set ws = Thisworkbook.sheets(1).name

Wasn't sure if you meant to indicate that for example my cell B1 could have the value "TEST"?

 

[Roderick_E]

Thanks for your quick reply. To be sure, would my syntax now read:

ThisWorkbook.Sheets(1).Name = ThisWorkbook.Sheets(1).Range("B1")
Set ws = Thisworkbook.sheets(1).name

Wasn't sure if you meant to indicate that for example my cell B1 could have the value "TEST"?

You're welcome. The "TEST" was an example how to rename the sheet directly. The next is to name it to the value of cell B1
You don't need the Set ws unless you have more to do with the worksheet after renaming it.

 

[grexcelman]

You're welcome. The "TEST" was an example how to rename the sheet directly. The next is to name it to the value of cell B1
You don't need the Set ws unless you have more to do with the worksheet after renaming it.

Actually looking to apply my code to the sheet's name, either that or to the active sheet but I wasn't sure how to express that syntax. Below is a snipet from my code that might help explain. Where I set ws, I'm getting tripped up on changing the sheet name without creating a version of this code for each sheet. I have several sheets and I plan to run this code in each sheet separately.

Sub CompareData()
  'Looks at cell and compares it against range average
  Dim CheckRow As Integer, ws As Worksheet
 
Set ws = ThisWorkbook.Worksheets("Systems")

For CheckRow = 17 To 64
  
    On Error Resume Next
    
    
    Td1 = Abs(ws.Range("E" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td2 = Abs(ws.Range("F" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td3 = Abs(ws.Range("g" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td4 = Abs(ws.Range("h" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td5 = Abs(ws.Range("i" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td6 = Abs(ws.Range("j" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td7 = Abs(ws.Range("k" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td8 = Abs(ws.Range("l" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td9 = Abs(ws.Range("m" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td10 = Abs(ws.Range("n" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td11 = Abs(ws.Range("o" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    Td12 = Abs(ws.Range("p" & CheckRow).Value) - (ws.Range("S" & CheckRow).Value)
    
   
    
    If Td1 > ws.Range("T" & CheckRow).Value Then
    
        ws.Range("E" & CheckRow).Select
       
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
        
    End With
 
           
    ElseIf Td2 > ws.Range("T" & CheckRow).Value Then
      
           
        ws.Range("F" & CheckRow).Select
       
    With Selection.Interior
         .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
        
    ElseIf Td3 > ws.Range("T" & CheckRow).Value Then
           
        ws.Range("g" & CheckRow).Select
       
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

 

[Roderick_E]

I think I misunderstood your original post. You don't want to rename a sheet, it sounds like you want to loop through sheets and apply coding.
See if this points you in the right direction

Sub WorksheetLoop2()
         ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet
         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets
            ' Insert your code here.
            ' This line displays the worksheet name in a message box.
            MsgBox Current.Name
         Next
      End Sub

 

[grexcelman]

I think I misunderstood your original post. You don't want to rename a sheet, it sounds like you want to loop through sheets and apply coding.
See if this points you in the right direction

Sub WorksheetLoop2()
         ' Declare Current as a worksheet object variable.
         Dim Current As Worksheet
         ' Loop through all of the worksheets in the active workbook.
         For Each Current In Worksheets
            ' Insert your code here.
            ' This line displays the worksheet name in a message box.
            MsgBox Current.Name
         Next
      End Sub

this worked perfectly. many thanks for your help.