Enter data in another sheet without moving to the sheet

[brianfosterblack]

I am running a macro in a worksheet - 12 set up for every month from March to February (Mar to Feb)
I have a hidden sheet set up for a log to record the date and time each time the macro is run
The log sheet is set up with each month heading in row 2 from Mar is column A to Feb in column L
I want to know if I am able to set up code to enter the timestamp in the log sheet under the relevant month (Now()) in the next empty row without having to go to the Log sheet
The logsheet uses a password BB2 to unprotect it.
Can anyone assist

 

[mumps]

Could you post the macro you are running in each sheet? Please use code tags when posting the code. Is the macro the same for each sheet? What is the sheet name of the log sheet?

 

[Anthony47]

In addition to what suggested by mumps (above), you can write to a sheet, even a hidden one, without selecting it, if you specify workboof and sheet name. For example:

Dim LogRow As Long, LogCol As Long
'
'...
LogCol = (Month(Date) + 12 - 3) Mod 12 + 1
LogRow = ThisWorkbook.Sheets("Log").Cells(Rows.Count, LogCol).End(xlUp).Row + 1
ThisWorkbook.Sheets("Log").Cells(LogRow, LogCol) = "What you  Wish"

To let the macro "work" on a protected sheet you can use the attribute UserInterfaceOnly. Example, add this WorkbookOpen macro into ThisWorkbook module of your file:

Private Sub Workbook_Open()
Sheets("Log").Unprotect Password:="MyPassword"
Sheets("Log").Protect Password:="MyPassword", UserInterfaceOnly:=True
End Sub

Bye

 

[brianfosterblack]

The log sheet is named "log"
I use the same macro on each of the 12 monthly sheets
You will have to tell me how to post my code using code tags

 

[mumps]

Click the "VBA" symbol in the menu and then copy/paste the code between the square brackets that appear.

 

[brianfosterblack]

Click the "VBA" symbol in the menu and then copy/paste the code between the square brackets that appear.

Thanks Mumps - I will know in future. The code provided by Anthony47 works perfectly. Thanks for the help.

 

[mumps]

You are very welcome. this would be my suggestion:

Sub InsertTimeStamp()
    Dim fnd As Range
    With Sheets("Log")
        .Unprotect Password:="BB2"
        Set fnd = .Rows(2).Find(ActiveSheet.Name, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            .Cells(.Rows.Count, fnd.Column).End(xlUp).Offset(1) = Now()
        End If
        .Protect Password:="BB2"
    End With
End Sub

Each time you run it, the timestamp will be inserted under the appropriate month based on the active sheet name.

 

[brianfosterblack]

In addition to what suggested by mumps (above), you can write to a sheet, even a hidden one, without selecting it, if you specify workboof and sheet name. For example:

Dim LogRow As Long, LogCol As Long
'
'...
LogCol = (Month(Date) + 12 - 3) Mod 12 + 1
LogRow = ThisWorkbook.Sheets("Log").Cells(Rows.Count, LogCol).End(xlUp).Row + 1
ThisWorkbook.Sheets("Log").Cells(LogRow, LogCol) = "What you  Wish"

To let the macro "work" on a protected sheet you can use the attribute UserInterfaceOnly. Example, add this WorkbookOpen macro into ThisWorkbook module of your file:

Private Sub Workbook_Open()
Sheets("Log").Unprotect Password:="MyPassword"
Sheets("Log").Protect Password:="MyPassword", UserInterfaceOnly:=True
End Sub

Bye

You are very welcome. this would be my suggestion:

Sub InsertTimeStamp()
    Dim fnd As Range
    With Sheets("Log")
        .Unprotect Password:="BB2"
        Set fnd = .Rows(2).Find(ActiveSheet.Name, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            .Cells(.Rows.Count, fnd.Column).End(xlUp).Offset(1) = Now()
        End If
        .Protect Password:="BB2"
    End With
End Sub

Each time you run it, the timestamp will be inserted under the appropriate month based on the active sheet name.

Thanks This works PERFECTLY. Thank you for taking the time. I really appreciate you guys and gals that do this

 

[mumps]

Glad to help.