Extract Comments in workbook to new worksheet with value from adjacent cell

[rrrexcel]

Hello -- I need to extract all the comments from all of the worksheets in my workbook, and I found the following <acronym title="visual basic for applications">VBA</acronym> code online which is great (How to Extract Comments in an Excel Sheet | TheAppTimes), but I need help to know if I can get the value of the cell to the right of the cell with the comment instead of the value of the cell with the comment.

Can you help? I thank you in advance.

For example: I am keeping track of time spent on a project.
V6=Start Time
X6=End Time (This column has the comment)
Y6=Total Minutes

The macro below brings back the value of X6, but I need to see the value of Y6.

Sheet	Address	Name	Value	Comment
12-19_12-25	$X$6		0.552083	RR: Month End

<tbody>
</tbody>

Macro found online:
Sub ShowCommentsAllSheets()
'modified from code
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim ws As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set newwks = Worksheets.Add
newwks.Range("A1:E1").Value = _
Array("Sheet", "Address", "Name", "Value", "Comment")
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
Set commrange = ws.Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If commrange Is Nothing Then
'do nothing
Else
i = newwks.Cells(Rows.Count, 1).End(xlUp).Row
For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.text
End With
Next mycell
End If
Set commrange = Nothing
Next ws
'format cells for no wrapping, remove line break
newwks.Cells.WrapText = False
newwks.Columns("E:E").Replace What:=Chr(10), _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Application.ScreenUpdating = True
End Sub​

 

[Jeffrey Smith]

How about something like this?

For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = mycell.Address
.Cells(i, 3).Value = mycell.Name.Name
.Cells(i, 4).Value = mycell.Value
.Cells(i, 5).Value = mycell.Comment.text
[COLOR=#ff0000].Cells(i, 5).Value = mycell.offset(0,1).value[/COLOR]
End With
Next mycell

 

[rrrexcel]

Thank you, Jeffery. With a couple of modifications, I was able to take your suggestion and make it work. I changed the new line to:
.Cells(i, 6).Value = mycell.offset(0,1).value

and changed references to any range of E:E to E:F.

Perfect solution. I am very grateful for your help!

 

[Jeffrey Smith]

You're welcome