CODEWITHSUNDEEP
arraysvbatabsrange
Journeyman
Journeyman

Reputation: 1

Excel - VBA code for sheetname change for sheets within a range (selected sheets as opposed to entire wb)

I have a macro that replaces all sheet names in a workbook w/ cell value B2 of each sheet:

Sub NamesWS()    

On Error Resume Next    

For Each ws In ThisWorkbook.Worksheets    

ws.Name = Left(ws.Cells(2, 2).Value, 31)    

Next    

On Error GoTo 0 

End Sub

It works beautifully but I want to change the code so that the macro only applies to sheets between 2 sheets (let's call them COST and PROJECT). Much appreciate your help. Thank you!

Upvotes: 0

Views: 42

Answers (2)

QHarr
QHarr

Reputation: 84465

Positional matching is risky but you might have something like:

Option Explicit
Public Sub NamesWS()
    Dim ws As Worksheet
    Dim bound1 As Long, bound2 As Long
    bound1 = ThisWorkbook.Worksheets("COST").Index
    bound2 = ThisWorkbook.Worksheets("PROJECT").Index

    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        Select Case True
        Case bound1 < bound2
            If ws.Index > bound1 And ws.Index < bound2 Then ws.Name = Left$(ws.Cells(2, 2).Value, 31)
        Case bound1 > bound2
            If ws.Index < bound1 And ws.Index > bound2 Then ws.Name = Left$(ws.Cells(2, 2).Value, 31)
        End Select
    Next
    On Error GoTo 0
End Sub

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166316

Sub NamesWS()    

    For Each ws In ThisWorkbook.Worksheets    
        If ws.Index > Sheets("COST").Index and ws.Index < Sheets("PROJECT").Index Then
            On Error Resume Next   
            ws.Name = Left(ws.Cells(2, 2).Value, 31)    
            On Error GoTo 0
        End If
    Next  

End Sub

Upvotes: 0

Related Questions