referencing another sheet
 

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

referencing another sheet
 

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

"James" wrote in message
ink.net...
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

referencing another sheet
 

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





"James" wrote in message
ink.net...
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

referencing another sheet
 

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)

"James" wrote in message
ink.net...
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

referencing another sheet
 

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.


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"James" wrote in message
ink.net...
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

referencing another sheet
 

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

"Patrick Molloy" wrote in message
...
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.


--
Patrick Molloy
Microsoft Excel MVP
---------------------------------
I Feel Great!
---------------------------------
"James" wrote in message
ink.net...
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