Change of Sheet Name

ganeshm · Oct 20, 2013

Below is the formula for a sample work book having sheet names - sheet1, sheet2, sheet3, sheet4 respectively.

If i change the name of the sheet to depts. say ADM, PRC, HSE; i receive debug msgs.

I believe highlighted places are the reason for the debug msg. Kindly, review and assist me in changing the sheet name.

Regards,
ganeshm

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
If Target.Cells.Count = 1 Then
If Target.Column = 5 Then
If InStr("2 5 6 7 8", Target.Row) > 0 Then
Application.EnableEvents = False

'retrieve data
Badgeno = .Range("e2")
foundSh = 0
For sh = 2 To 4
Set found = Sheets("sheet" & sh).Range("b:b").Find(Badgeno, LookIn:=xlValues)
If Not found Is Nothing Then
foundSh = sh
foundRow = found.Row
Exit For
End If
Next sh

.Range("f2") = ""
If foundSh > 0 Then
If Target.Row = 2 Then
For c = 1 To 5
.Cells(c + 4, "e") = Sheets("sheet" & foundSh).Cells(foundRow, c + 2)
Next
Else
For c = 1 To 5
Sheets("sheet" & foundSh).Cells(foundRow, c + 2) = .Cells(c + 4, "e")
Next
End If
Else
.Range("f2") = "BadgeNo. not found"
.Range("e5") = ""
.Range("e6") = ""
.Range("e7") = ""
.Range("e8") = ""

End If

Application.EnableEvents = True
End If
End If
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Debraj · Oct 20, 2013

Try this..

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
    If Target.Cells.Count = 1 Then
        If Target.Column = 5 Then
            If InStr("2 5 6 7 8", Target.Row) > 0 Then
                Application.EnableEvents = False
             
                'retrieve data
                Badgeno = .Range("e2")
                foundSh = 0
                    For Sh = 2 To 4
                    Set found = Sheets(Sh).Range("b:b").Find(Badgeno, LookIn:=xlValues)
                    If Not found Is Nothing Then
                        foundSh = Sh
                        foundRow = found.Row
                        Exit For
                    End If
                    Next Sh
             
                .Range("f2") = ""
                If foundSh > 0 Then
                    If Target.Row = 2 Then
                        For c = 1 To 5
                            .Cells(c + 4, "e") = Sheets(foundSh).Cells(foundRow, c + 2)
                        Next
                    Else
                        For c = 1 To 5
                            Sheets(foundSh).Cells(foundRow, c + 2) = .Cells(c + 4, "e")
                        Next
                    End If
                Else
                    .Range("f2") = "BadgeNo. not found"
                    .Range("e5") = ""
                    .Range("e6") = ""
                    .Range("e7") = ""
                    .Range("e8") = ""
                End If
                Application.EnableEvents = True
            End If
        End If
    End If
End With
End Sub

for more detail.. check this post..
http://chandoo.org/forum/threads/how-to-edit-my-sheet-name-in-macro.12652/#post-74691

ganeshm · Oct 20, 2013

Thank you, it works.

ganeshm · Oct 20, 2013

hi deb,

I tried retrieving values by leaving a row after other. But i receive the values in the very next row (like E5,E6,E7, etc...)

For instance, when i retrieve - i want the values to display in "E5" , "E7", "E8" something like that.

Is here the change i have to make -

Set found = Sheets(Sh).Range("b:b").Find(Badgeno, LookIn:=xlValues)
If Not found Is Nothing Then
foundSh = Sh
foundRow = found.Row

.Range("f2") = ""
If foundSh > 0 Then
If Target.Row = 2 Then
For c = 1 To 6
.Cells(c + 4, "e") = Sheets(foundSh).Cells(foundRow, c + 2)
Next
Else
For c = 1 To 6
Sheets(foundSh).Cells(foundRow, c + 2) = .Cells(c + 4, "e")
Next

Debraj · Oct 20, 2013

Hi Ganesh,

You are trying to retrieve data from multiple sheet, like Vlookup and transpose..
At the same time your code, works as Full-Duplex, i mean data update in both direction.

A blind shot, without seeing actual structure..

EDIT: updated in below post..

ganeshm · Oct 20, 2013

Here i have posted the excel file.....
so that u have a clear idea

Debraj · Oct 20, 2013

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
    If Target.Cells.Count = 1 Then
        If Target.Column = 5 Then
            If InStr("2 5 6 7 8", Target.Row) > 0 Then
                Application.EnableEvents = False
             
                'retrieve data
               Badgeno = .Range("e2")
                foundSh = 0
                    For Sh = 2 To 4
                    Set found = Sheets(Sh).Range("b:b").Find(Badgeno, LookIn:=xlValues)
                    If Not found Is Nothing Then
                        foundSh = Sh
                        foundRow = found.Row
                        Exit For
                    End If
                    Next Sh
             
                .Range("f2") = ""
                If foundSh > 0 Then
                    If Target.Row = 2 Then
                        For c = 1 To 5
                            .Cells((c * 2) + 3, "e") = Sheets(foundSh).Cells(foundRow, c + 2)
                        Next
                    Else
                        For c = 1 To 5
                            Sheets(foundSh).Cells(foundRow, c + 2) = .Cells((c * 2) + 3, "e")
                        Next
                    End If
                Else
                    .Range("f2") = "BadgeNo. not found"
                    .Range("e5") = ""
                    .Range("e6") = ""
                    .Range("e7") = ""
                    .Range("e8") = ""
                End If
                Application.EnableEvents = True
            End If
        End If
    End If
End With
End Sub

ganeshm · Oct 20, 2013

Solved. Thanks Deb.

Actually, i missed this (c * 2) logic.

SirJB7 · Oct 22, 2013

ganeshm said:
Below is the formula for a sample work book having sheet names - sheet1, sheet2, sheet3, sheet4 respectively.

@ganeshm
Hi!
Just for a matter of accuracy, there's no formula at all but VBA code only.
Regards!

ganeshm · Oct 22, 2013

@SirJB7

Got that, thank you for correcting.

SirJB7 · Oct 23, 2013

@ganeshm
Hi!
So as people who read the title don't get confused when not finding any formula, but code instead.
Regards!

ganeshm · Oct 27, 2013

hi deb,

There is a slight problem with the Excel sheet, (highlighted in yellow).
When the badge no. is entered in "e2", say 329 - the corresponding data is not shown. It gives data of 104329, which is wrong. Is there anyway, i resolve this?
It happens to all 3 digit and 6 digit nos.
for e.g. 329 / 104329
335 / 104335, etc........

regards,
Safiu

Marc L · Oct 27, 2013

Just read integrated VBA help of Range.Find method and thus update its LookAt parameter …

Debraj · Oct 27, 2013

Hi ganesh,

Marc gives you right direction..
change Find section to this..

Code:

Set found = Sheets(Sh).Range("b:b").Find(Badgeno, LookIn:=xlValues, Lookat:=xlWhole)

ganeshm · Oct 27, 2013

Thanks marc,
Thank u Deb...
Solved.

ganeshm · Oct 27, 2013

If Lookat:=xlWhole is executed, Else condition is not working in the sheet.

Debraj · Oct 27, 2013

try this...

Please next time.. concentrate on giving proper title for your post..
first 3 post are related to "Change of Sheet Name", and all other are related to "Vlookup value using find method"

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Raj
    Application.EnableEvents = False
'----
    If Not Intersect(Target, Range("$E$2,$E$5,$E$7,$E$9,$E$11,$E$13")) Is Nothing Then
        If Target.Cells.Count = 1 Then
            Badgeno = [E2]
            For sh = 2 To Sheets.Count
                    Set found = Sheets(sh).Range("b:b").Find(Badgeno, LookIn:=xlValues, Lookat:=xlWhole)
                If Not found Is Nothing Then
                    foundSh = sh
                    [f2] = ""
                    foundRow = found.Row
                    Exit For
                End If
            Next sh
           
            If foundSh > 0 Then
            Select Case Target.Address
                Case "$E$2"
                    For c = 1 To 5
                        Cells((c * 2) + 3, "e") = Sheets(foundSh).Cells(foundRow, c + 2)
                    Next
                Case Else
                    For c = 1 To 5
                        Sheets(foundSh).Cells(foundRow, c + 2) = Cells((c * 2) + 3, "e")
                    Next
            End Select
            Else
                Range("$E$5,$E$7,$E$9,$E$11,$E$13") = ""
                Range("F2") = "Not Found"
            End If
        End If
    End If
'----
Deb:
    Application.EnableEvents = True
    Exit Sub
Raj:
    MsgBox Err.Description
    Resume Deb
End Sub

ganeshm · Oct 28, 2013

@ganeshm

Thanks deb.

In fact, i tried changing the name soon after SirJB7 intimated me. But i didn't have the option to do so.
Next time, i will.

Change of Sheet Name

ganeshm

Member

Debraj

Excel Ninja

ganeshm

Member

ganeshm

Member

Debraj

Excel Ninja

ganeshm

Member

Attachments

Debraj

Excel Ninja

ganeshm

Member

SirJB7

Excel Rōnin

ganeshm

Member

SirJB7

Excel Rōnin

ganeshm

Member

Attachments

Marc L

Excel Ninja

Debraj

Excel Ninja

ganeshm

Member

ganeshm

Member

Debraj

Excel Ninja

ganeshm

Member