ROBJ
12-27-2017, 01:05 AM
Hello & Happy Holidays
I started the below VBA code to find a specific sheet based on a condition (if the last letter of the sheet meets that condition, in this case = A).
Formula in cell O3 pulls the name of the sheet=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Formula in cell U3 gets rid of the last letter (A) from the sheet name=LEFT(O3,LEN(O3)-1) ....
Column T (cells T5 to T200) contains the name of the rest of the sheets in the WB. What would be the most efficient way to find the cell in range T5:T200 that matches the value of cell U3 and replace the cell immediately below it with the value from cell O3?
Thank you in advance.
Sub pickSheet()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pwd As String
pwd = "password"
For Each ws In Worksheets
ws.Unprotect Password:=pwd
Next ws
For Each ws In ThisWorkbook.Worksheets
If Right(ws.Name, 1) = "A" Then
ws.Activate
End If
Range("U3").Formula = "=LEFT(O3,LEN(O3)-1)"
Range("O3").Select
Next ws
For Each ws In Worksheets
ws.Protect Password:=pwd
Next ws
End Sub
I started the below VBA code to find a specific sheet based on a condition (if the last letter of the sheet meets that condition, in this case = A).
Formula in cell O3 pulls the name of the sheet=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)
Formula in cell U3 gets rid of the last letter (A) from the sheet name=LEFT(O3,LEN(O3)-1) ....
Column T (cells T5 to T200) contains the name of the rest of the sheets in the WB. What would be the most efficient way to find the cell in range T5:T200 that matches the value of cell U3 and replace the cell immediately below it with the value from cell O3?
Thank you in advance.
Sub pickSheet()
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim pwd As String
pwd = "password"
For Each ws In Worksheets
ws.Unprotect Password:=pwd
Next ws
For Each ws In ThisWorkbook.Worksheets
If Right(ws.Name, 1) = "A" Then
ws.Activate
End If
Range("U3").Formula = "=LEFT(O3,LEN(O3)-1)"
Range("O3").Select
Next ws
For Each ws In Worksheets
ws.Protect Password:=pwd
Next ws
End Sub