Storing the history of last seen sheets in a workbook!

[nagovind]

Dears,

Is there is any way to keep the history of last seen sheets in workbook containing more than 100 sheets ---- in a separate sheet called historysheet

Say i have sheet1 sheet2 sheet3....sheet100

if sheet1 is activated then the A1 cell in the sheet named "historysheet" will be updated with the name sheet1

after if i select sheet5 then the A2 cell in the sheet named "historysheet" will be updated with the name sheet5

after if i select sheet16 then the A3 cell in the sheet named "historysheet" will be updated with the name sheet16

and so on

Atleast no. of stored history will be twice the no. of sheets in the workbook.

Please do the needful

Regards
Govind

 

[Marc L]

Hi !

Use Workbook_SheetChange event (ThisWorkbook module)
to store the worksheet (Sh object) name …
​

 

[YasserKhalil]

Hi
Try the following in workbook module

Const sSheet As String = "Watch"

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Ws      As Worksheet
    Dim LR      As Long
   
    If Sh.Name <> sSheet Then
        With Sheets(sSheet)
            LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1

            If Target.Column < 10 And Target.Row > 1 Then
                .Cells(LR, 1) = ActiveSheet.Name
                .Cells(LR, 4) = Target.AddressLocal
                .Cells(LR, 7) = Target.Value
                .Cells(LR, 6) = .Cells(1, Columns.Count)
                .Cells(LR, 5) = Application.UserName
                .Cells(LR, 2) = Format(Date, "dd-mm-yyyy")
                .Cells(LR, 3) = Format(Now, "h:mm:ss")
            End If
        End With
    End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Sheets(sSheet).Cells(1, Columns.Count).Value = ActiveCell.Value
End Sub

 

[nagovind]

Dear YasserKhalil,
Thank you
Actually i'm looking for the history of sheets internal to a workbook so the history has to store the previous work'sheets ' in each cell..in any one of the sheet

Dear Marc,
Thanks for your suggestions
I'm trying my best ,..

Regards
Govind

 

[Marc L]

Sorry, it was a wrong event, warming a couple of neurones :​

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Worksheets("historysheet").Cells(Rows.Count, 1).End(xlUp)(2).Value = Sh.Name
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[nagovind]

Yes it is working

THANK YOU SO MUCH Marc
Regards
Govind