Renaming sheet based on offset cell vlaue

[riazd]

Hi

I'm pretty new to VBA and need some assistance to rename a sheet based on

1. finding a value in the sheet
2. Offsetting 3 cells to the right of the found cell
3. using the value of the offset cell to rename the sheet

Thus far i've been able to find the cell & allocate the value of the offset cell to a string using the following code viz.

Sub find()


Dim rs As Worksheet
Dim rgFound As Range
Dim EmpNo As String


Set rgFound = Range("A1:AU57").find("Empl. No. :")
EmpNo = Cells(rgFound.Row, rgFound.Column + 3)


rs.Name = EmpNo


End Sub


However the rs.Name = EmpNo gives me an error Run time error '91':
Object variable or with block variable not set

Any help will be appreciated

 

[James_Latimer]

Are you wanting to add a new sheet and name it or just rename an existing sheet? If it's the latter then which sheet is being renamed?

 

[Fluff]

You haven't given rs a value.
Are you trying to rename the active sheet?

 

[Joe4]

That is because you haven't sent rs equal to anything yet.
So you are trying to assign a sheet name, to a sheet that has not been defined yet (it is asking you "what sheet"?).

You need something like this first:

Set rs = ActiveSheet

 

[Fluff]

If it is the active sheet try

Sub FindRename()
   Dim rgFound As Range
   
   Set rgFound = Range("A1:AU57").find("Empl. No. :", , , , , , False, , False)
   If Not rgFound Is Nothing Then ActiveSheet.name = rgFound.Offset(, 3).Value
End Sub

 

[riazd]

Are you wanting to add a new sheet and name it or just rename an existing sheet? If it's the latter then which sheet is being renamed?

Hi James

I'm trying to rename all the existing sheets in a workbook,

Thanx

 

[riazd]

Hi Fluff

I'm trying to rename all the sheets in a workbook, i've updated the script to include the part where it is supposed to update all sheets

Sub find()


Dim rs As Worksheet
Dim rgFound As Range
Dim EmpNo As String




For Each rs In Sheets
Set rs = ActiveSheet
Set rgFound = Range("A1:AU57").find("Empl. No. :")
EmpNo = Cells(rgFound.Row, rgFound.Column + 3)
rs.Name = EmpNo
Next rs


End Sub

 

[Joe4]

A few things, if you are looping through your sheets, you do not want to set rs to the ActiveSheet, because the ActiveSheet is whatever sheet you start on and will not change in your code.
Also, don't use reserved words like "find" as the name of procedures or variables.

Try this:

[COLOR=#333333]Sub MyFind()[/COLOR]

[COLOR=#333333]Dim rs As Worksheet[/COLOR]
[COLOR=#333333]Dim rgFound As Range[/COLOR]
[COLOR=#333333]Dim EmpNo As String[/COLOR]


[COLOR=#333333]For Each rs In Sheets[/COLOR]
[COLOR=#333333]    Set rgFound = rs.Range("A1:AU57").find("Empl. No. :")[/COLOR]
[COLOR=#333333]    EmpNo = rs.Cells(rgFound.Row, rgFound.Column + 3)[/COLOR]
[COLOR=#333333]    rs.Name = EmpNo[/COLOR]
[COLOR=#333333]Next rs[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[riazd]

Hi Joe

Thanx for the code, it works perfectly, i really appreciate your assistance,

Have a wunnerful day

 

[Joe4]

You are welcome.