Loop in multiple sheets

[shwetankbhardwaj]

Hi there,

I am using the below codes to get the job done.

Sub Updatesheetname()

Dim sht As Worksheet

For Each sht In Worksheets

sht.Range("F2").Formula = "=MID(CELL(""filename"",R[211]C[-5]),FIND(""]"",CELL(""filename"",R[211]C[-5]))+1,31)"
sht.Range("F2").Copy
Range("E2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.End(xlUp)).Select
sht.Paste

Next sht

End Sub


But the problem here is, its not working for all the worksheets. I want this coding to work with all the sheets (except "Master workbook" coz this contains macro buttons).

Thanks in advance!

Thanks,
Shwetank

 

[Craggs82]

Sub Updatesheetname()

Dim twb As Workbook
Dim sht As Worksheet
Dim lrow As Long

Set twb = ThisWorkbook
    
    For Each sht In twb.Worksheets
        
        If sht.Name <> "Master workbook" Then
        
            lrow = sht.Range("E2").End(xlDown).Row
            sht.Range("F2:F" & lrow).Formula = "=MID(CELL(""filename"",R[211]C[-5]),FIND(""]"",CELL(""filename"",R[211]C[-5]))+1,31)"
        
        End If
        
    Next sht

End Sub

not sure about that formula though.

 

[shwetankbhardwaj]

Hi Craggs82,

This is amazing!

It is working as I want, just a small problem, one of the tabs don't have any data in E column but F column fetched till end (Sheet name through value), however there shouldn't be any value in F column since there are no data in E column.

Can this be fixed too?

Thanks,
Shwetank

 

[Craggs82]

If the data usually starts in E2 you could clarify it with another IF statment.

Sub Updatesheetname()

Dim twb As Workbook
Dim sht As Worksheet
Dim lrow As Long

Set twb = ThisWorkbook
    
    For Each sht In twb.Worksheets
        
        If sht.Name <> "Master workbook" Then
            
            If sht.Range("E2") <> "" Then
                lrow = sht.Range("E2").End(xlDown).Row
                sht.Range("F2:F" & lrow).Formula = "=MID(CELL(""filename"",R[211]C[-5]),FIND(""]"",CELL(""filename"",R[211]C[-5]))+1,31)"
            End If
            
        End If
        
    Next sht

End Sub

 

[shwetankbhardwaj]

Thanks Craggs82,

Appreciate your quick response. Thanks alot!