Use latest sheet in formula vba-CodePudding

I am using an excel sheet that should reference to the latest sheet in a series Inlife (n), I got the VBA so that it replaces the reference if I manually tell it which sheet to use. I cannot simply use the last sheet or the 5th of all sheets or something. However the position is always the sheet before sheet RG. I tried to use the same code as for copy and paste: =before... but this didn't get me anywhere, neither did count like below.


   Dim result As Integer 
   result = Count(If ws.Name = "Inlife" Or ws.Name Like "Inlife (*)")

ActiveSheet.Select

        Dim rng As Range, cel As Range
    Set rng = ActiveSheet.Range("B3:B25")
    

    For Each cel In rng
    cel.Formula = Replace(cel.Formula, "Inlife", "'Inlife (result)'")

    Next

This doesn't work at all but I hope you understand my intention. (I am sure this looks ridiculous but I do not know how else to describe my problem)

I hope you can help me

CodePudding user response：

You can loop over the worksheets in order, assigning the latest worksheet to a variable if its name begins with "Inlife". When the loop completes, the variable will be set to the last sheet whose name begins with "Inlife".

Sub getinlife()

    Dim last_inlife_sheet As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
        ' Check if worksheet name begins with "Inlife"
        If InStr(1, ws.Name, "Inlife") = 1 Then
            Set last_inlife_sheet = ws
        End If
    Next ws
    
    (your code)
    
End Sub

I tested this using a workbook I mocked up with the following sheet names:

and verified that after running the loop, last_inlife_sheet was set to the sheet named Inlife (3).

CodePudding user response：

If "sheet RG" means a sheet named "RG", please try the next way:

Dim ws As Worksheet
Set ws = Worksheets("RG").Previous
For Each cel In rng
  cel.Formula = Replace(cel.Formula, "Inlife", "'" & ws.name & "'")
Next