renaming multiple sheets in workbook

[jung5290]

Hi All,
Hope someone can help me out with the code that i'm trying to solve debug for.
What i'd like to achieve is that I would like to change the worksheet names for all sheets in workbook. I am copying 39 files and consolidating them into a workbook.
I got my coding worked out for consolidating 39 source files to 1 workbook but I'm trying to rename original worksheet names from source files.
It would either contain worksheet name with dynamic or Target. If the name of worksheet contains word like dynamics i want to change the worksheet name to left(range("b6"),4) else worksheet name to left(range("a6"),4) and i want to apply to all sheets in the workbook. But for some reason it gets stuck in first tab it only changed first tabs' name as i expected but its not changing preceding tabs and giving me applicated defined and object defined error my codes are as per below.


Set wbN = ActiveWorkbook

For Each Sheet In ActiveWorkbook.Worksheets

If Sheet.Name Like "*dynamic*" Then
Sheet.Name = Right(Range("B6"), 4)

Else

Sheet.Name = Left(Range("a6"), 4)

End If

Next Sheet

End Sub

Thanking you in advance for your help,

 

[NdNoviceHlp]

A few things, first please use code tags. You should always declare (Dim) your variables. U have not said from which sheet your range name comes from. I'll try sheet1. HTH. Dave

Sub test()
Dim Sht As Worksheet, ThisSht As Worksheet, Wbn As Workbook
Set Wbn = ActiveWorkbook
Set ThisSht = Wbn.Sheets("Sheet1")

For Each Sht In Wbn.Worksheets
If Sht.Name Like "*dynamic*" Then
Sht.Name = Right(ThisSht.Range("B" & 6), 4)
Else
Sht.Name = Left(ThisSht.Range("A" & 6), 4)
End If
Next Sheet
End Sub

 

[jung5290]

A few things, first please use code tags. You should always declare (Dim) your variables. U have not said from which sheet your range name comes from. I'll try sheet1. HTH. Dave

Sub test()
Dim Sht As Worksheet, ThisSht As Worksheet, Wbn As Workbook
Set Wbn = ActiveWorkbook
Set ThisSht = Wbn.Sheets("Sheet1")

For Each Sht In Wbn.Worksheets
If Sht.Name Like "*dynamic*" Then
Sht.Name = Right(ThisSht.Range("B" & 6), 4)
Else
Sht.Name = Left(ThisSht.Range("A" & 6), 4)
End If
Next Sheet
End Sub

Thanks for your help and i will make sure to use code tags. I tried the above code and i get object defined error…

 

[jung5290]

Thanks for your help!! and i will make sure to use code tags. I tried the above code and i get object defined error…my sheet starts from the first sheet

 

[Peter_SSs]

left(range("b6"),4) else worksheet name to left(range("a6"),4)

Are A6 and B6 being referred to here on the worksheet being renamed? If so try

Sub RenameSheets()
  Dim ws As Worksheet
 
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Name Like "*dynamic*" Then
      ws.Name = Right(ws.Range("B6"), 4)
    Else
      ws.Name = Left(ws.Range("A6"), 4)
    End If
  Next ws
End Sub

@NdNoviceHlp
A few things
- Even better than standard code tags are the specific vba code tags or the rich code tags.
- You have changed the variable 'Sheet' to 'Sht' ... but not everywhere.
- If you are using A6 and B6 on 'Sheet1' to rename the others, then the code will error if there is more than one 'dynamic' worksheet or more than one non-'dynamic' worksheet since it would be trying to name two sheets the same.

 

[NdNoviceHlp]

Thanks Peter. I missed the logic re. naming from the active sheet instead of just 1 sheet ....makes sense that the naming must come from the active sheet. Whoops on the sht/sheet thing. Jung5290 I'm sure Peter has U fixed up. Dave

 

[Peter_SSs]

I missed the logic re. naming from the active sheet instead of just 1 sheet ....makes sense that the naming must come from the active sheet.

I guess that the names could all come from one sheet, but the cells from which the names are derived would all have to be different. eg down the columns.
Since all sheets in the workbook are being renamed and that would include the one with the name data I though that scenario unlikely.
Anyway, hopefully we will get confirmation or otherwise from the OP in due course. ?