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,
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,