Evaluate if a sheet exists using tab index???

[MBellew1463]

So I have the below code written to evaluate if a specific sheet name exists and if so transfer some data - works great BUT now they have changed the way the report comes out and I need to be able to do the same thing with a "dynamic" sheet name so thought it would be best to use the tab index but I have not been able to figure out how to go about this... The code below is just an excerpt as it rolls through 100 different sheets but if I can get it to do one then I can fix the rest: "Sheet1" is actually tab 4

With Sheets("JSR SUMMARY")
If Evaluate("isref('sheet1'!A1)") Then
Sheets("Sheet1").Range("G4").Value = .Range("T4").Value
End If
If Evaluate("isref('sheet1'!A1)") Then
Sheets("Sheet1").Range("G5").Value = .Range("T4").Value
End If
If Evaluate("isref('sheet2'!A1)") Then
Sheets("Sheet2").Range("G4").Value = .Range("T5").Value
End If
If Evaluate("isref('sheet3'!A1)") Then
Sheets("Sheet3").Range("G4").Value = .Range("T6").Value
End If
If Evaluate("isref('sheet4'!A1)") Then
Sheets("Sheet4").Range("G4").Value = .Range("T7").Value
End If
If Evaluate("isref('sheet5'!A1)") Then
Sheets("Sheet5").Range("G4").Value = .Range("T8").Value
End If

 

[My Aswer Is This]

Will this do what you want:

Sub Copy_Using_Index()
'Modified 7/6/2022  2:03:50 PM  EDT

With Sheets("JSR SUMMARY")
    Sheet3.Range("G4").Value = .Range("T4").Value
End With

End Sub

 

[MBellew1463]

Will this do what you want:

Sub Copy_Using_Index()
'Modified 7/6/2022  2:03:50 PM  EDT

With Sheets("JSR SUMMARY")
    Sheet3.Range("G4").Value = .Range("T4").Value
End With

End Sub

This will work for the data to be pulled from the indexed tab but throws an error if that tab does not exist - i need to be able to test if the tab exists and then transfer the data

 

[My Aswer Is This]

Try this:

Sub Copy_Using_Index()
'Modified  7/6/2022  2:48:47 PM  EDT
    On Error GoTo M
    
    With Sheets("JSR SUMMARY")
        Sheet3.Range("G4").Value = .Range("T4").Value
    End With
Exit Sub
M:
    MsgBox "That sheet does not exist"
End Sub

 

[My Aswer Is This]

If you're doing this on all sheets in your workbook, we can just tell the script to loop trough all sheets

Like for i = 1 to sheets. Count
This means go from sheet 1 to the last sheet in the workbook.
We do not need sheet names or index numbers.

 

[My Aswer Is This]

If you want to do this on all sheets in your workbook, try this:

Sub Copy_Using_Index()
'Modified  7/6/2022  3:31:47 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
   
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "JSR SUMMARY" Then
            Sheets(i).Range("G4").Value = Sheets("JSR SUMMARY").Range("T4").Value
    End If
    Next
Application.ScreenUpdating = True
End Sub

 

[MBellew1463]

If you want to do this on all sheets in your workbook, try this:

Sub Copy_Using_Index()
'Modified  7/6/2022  3:31:47 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
  
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "JSR SUMMARY" Then
            Sheets(i).Range("G4").Value = Sheets("JSR SUMMARY").Range("T4").Value
    End If
    Next
Application.ScreenUpdating = True
End Sub

I actually came up with a modified version of this reply but either will work just fine - thanks!!

Dim i As Integer
Dim j As Integer

j = ThisWorkbook.Sheets.Count - 2

With Sheets("JSR SUMMARY")
For i = 4 To j
Sheets(i).Range("F4").Value = .Range("T" & i).Value
Next i
End With

 

[My Aswer Is This]

I actually came up with a modified version of this reply but either will work just fine - thanks!!

Dim i As Integer
Dim j As Integer

j = ThisWorkbook.Sheets.Count - 2

With Sheets("JSR SUMMARY")
For i = 4 To j
Sheets(i).Range("F4").Value = .Range("T" & i).Value
Next i
End With

Glad you have it the way you want. Take care