Evaluate if a sheet exists using tab index???

MBellew1463

New Member
Joined
Jun 8, 2022
Messages
10
Office Version
  1. 2021
Platform
  1. Windows
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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Will this do what you want:
VBA Code:
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
 
Upvote 0
Will this do what you want:
VBA Code:
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
 
Upvote 0
Try this:
VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
If you want to do this on all sheets in your workbook, try this:
VBA Code:
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
 
Upvote 0
Solution
If you want to do this on all sheets in your workbook, try this:
VBA Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top