Help in VBA.

[YY san.]

Dear all experts,
I am totally new to VBA. I have make use of a set of code provided by Mr.
Paul Sheppard. But I am encountering errors.

What I want to do is:
To update the 13 worksheet's name referencing to another file called Staff
Names for timesheet.xls, worksheet name called "IT" and from cell b2 ~ B14
This Staff Names for timesheet.xls path is C:\Document folder\Timesheet


Sub namesheets()
Dim arr As Variant
arr = Workbooks("Staff Names for
timesheet.xls").Worksheet("IT").Range("b2:b14").Value
For i = LBound(arr) To UBound(arr)
Sheets(i + 1).Activate
Sheets(i).Name = arr(i, 1)
Next i
End Sub

This is definitely a simple questions to the experts out there, appreciate
very much.
Thanks!

 

[Dave Peterson]

Maybe...

Sub namesheets()
Dim arr As Variant
arr = Workbooks("Staff Names for timesheet.xls") _
.Worksheet("IT").Range("b2:b14").Value
For i = LBound(arr, 1) To UBound(arr, 1)
Sheets(i).Name = arr(i, 1)
Next i
End Sub

If this doesn't help, maybe you can explain what's going wrong.

 

[YY san.]

Thanks Dave! It works(except "Worksheet" encoutered an error. But it is OK
after I have it changed to "Worksheet").
Another question: What is the syntax if I want to specify the path where
this "Staff Names for timesheet.xls" is located? I realised that if I dont
open this file at the same time, it will not work.
Thanks again.

 

[Dave Peterson]

I didn't notice that typo in the original post. Glad you caught it.

But you don't specify the path in the workbooks() collection. The workbook has
to be open before you can use your code. But you could open it in your macro:

Sub namesheets()
dim TSWkbk as workbook
Dim arr As Variant

set tswkbk = nothing
on error resume next
set tswkbk = workbooks("staff names for timesheet.xls")
on error goto 0

if tswkbk is nothing then
set tswkbk _
= workbooks.open(filename:="C:\yourpath\Staff Names for timesheet.xls")
end if

arr = tswkbk.Worksheets("IT").Range("b2:b14").Value
For i = LBound(arr, 1) To UBound(arr, 1)
Sheets(i).Name = arr(i, 1)
Next i

End Sub

 

[YY san.]

Thanks Dave. It works perfect!!
However, I have another simple question:
If I want the macro to update the worksheet name from Sheet2(skip Sheet1).
How can I code this?
regards,

 

[Dave Peterson]

Maybe...

For i = LBound(arr, 1) To UBound(arr, 1)
Sheets(i+1).Name = arr(i, 1)
Next i

Thanks Dave. It works perfect!!
However, I have another simple question:
If I want the macro to update the worksheet name from Sheet2(skip Sheet1).
How can I code this?
regards,