referencing another sheet

[James]

Hello NG,
can someone please help me with problem in regards to referencing another
worksheet?
I have a worksheet called "log" which keeps track of the date the last time
the workbook was modified.
I also have many worksheets for each month of the years, (i.e. jan2004,
feb2004, mar2004...).
What I would like to do is display a message showing the date the last time
the workbook is modified.
I can get everything to work but I always end up on the log sheet, how can I
sat on the current month's page and reference the Log sheet?
Any suggestions would be greatly appreciated.
Thanks
James

This works====================================================

Sub LastModified()

Dim datLastModified As Date
Dim datToday As Date
Dim intLastCell As Integer

datToday = Date
Application.ScreenUpdating = False

Worksheets("Log").Select
Range("=Log!A1").Select
ActiveCell.SpecialCells(xlLastCell).Select
datLastModified = ActiveCell.Value
ActiveCell.Offset(1, 0) = datToday

Application.ScreenUpdating = True

MsgBox "Last modified on: " & datLastModified, vbOKOnly, "Last Modified"

End Sub

 

[Ed]

Hi, James. The following code picks up a value from a cell on another
worksheet and displays it in a message box. It can be run from any
worksheet, and does not take you to the referenced sheet.

HTH
Ed

Sub Whats_This()
Dim strThis As String
strThis = Worksheets("Sheet1").Range("D9").Value
MsgBox strThis
End Sub

 

[Neil]

James,

Try this - untested,

Dim datLastModified As Date
Dim datToday As Date
Dim intLastCell As Integer
Dim LastRow as long
datToday = Date
Application.ScreenUpdating = False
Lastrow=sheets("Log").cells(65536,1).end(XLup).row
datLastModified = sheets("Log").range("A" & Lastrow).Value
Sheets("Log").range("A" & Lastrow + 1).value = DatToday
Application.ScreenUpdating = True
MsgBox "Last modified on: " & datLastModified, vbOKOnly, "Last Modified"

Neil

 

[Bob Phillips]

Hi James,

Try this

Sub LastModified()
Dim datLastModified As Date
Dim datToday As Date
Dim intLastCell As Integer
Dim sSheet As String

datToday = Date
Application.ScreenUpdating = False

Worksheets("Log").Select
With Range("A1").SpecialCells(xlLastCell)
datLastModified = .Value
sSheet = Format(datLastModified, "mmmyyyy")
.Offset(1, 0) = datToday
End With

Application.ScreenUpdating = True

MsgBox "Last modified on: " & datLastModified, vbOKOnly, "Last Modified"

Worksheets(sSheet).Activate

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Patrick Molloy]

You can achieve this easily with the worksheet change event in
ThisWorkbook's code page. Its like a change event in a sheet, but its at the
workbook level. This means that not only is the changed cell passed to the
event handler, so is the worksheet.

In the VBE, in the Project viewer, double click on ThisWorkbook to open the
code page and paste this:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LogTarget As Range

On Error GoTo error_trap

' check its not the log - if it is kick out...
If Sh.Name = "log" Then Exit Sub

' get the next row in the log file
Set LogTarget = Worksheets("log").Range("A65000").End(xlUp).Offset(1, 0)

' enter the log data :
LogTarget.Value = Format(Now, "dd-mm-yy HH:MM")
LogTarget.Offset(0, 1) = Sh.Name
LogTarget.Offset(0, 2) = Target.Address
LogTarget.Offset(0, 3) = Target.Value

Exit Sub
error_trap:
MsgBox Err.Description

End Sub


What we expect is to find a sheet called "log". We test that this isn't the
sheet name being passed to prevent an endless loop resulting in a stack
overflow (geeky huh <vbg>)
Any way, if the changed sheet isn't the log sheet itself, the the next
available row on the log sheet is found and data regarding the change is
placed there.

 

[James]

To Ed, Neil, Bob and Patrick,
Thanks to all of you for the great tips, I didn't know there was so many
ways to do the same thing ;-0
I'll be able to put to use some of the other tips I also learned from your
coding.
Anyway thanks,
James