tying cells together for automatic updating

[Sanchaz]

I finally got my summary report to work. Now my manager wants to be able to edit cells in the summary report and have that change automatically updated to the source worksheet/cell. So, what I think I need to do is tie the source cell to the destination cell as I create the summary report. I guess the update should/would occur on Save.

Here's my code:

Option Explicit
Sub CreateSummaryReport()
Dim wrk As Workbook 'Workbook object - Always good to work with object variables
Dim sht As Worksheet 'Object for handling worksheets in loop
Dim trg As Worksheet 'Summary Report Worksheet
Dim rng As Range 'Range object
Dim rng2 As Range 'Range object
Dim iLastRow As Long
Dim colCount As Integer 'Column count in tables in the worksheets
Dim rowCount As Integer 'Row count in tables in the worksheets
Dim rowCount2 As Integer 'Row count in tables in the worksheets
Dim i As Long
Dim count As Integer
Set wrk = ActiveWorkbook 'Working in active workbook

'Checks for an existing Summary report and displays a msg if there is
For Each sht In wrk.Worksheets
If sht.Name = "Summary Report" Then
MsgBox "There is a worksheet called as 'Summary Report'." & vbCrLf & _
"Please remove or rename this worksheet since 'Summary Report' would be" & _
"the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error"
Exit Sub
End If
Next sht

'We don't want screen updating
Application.ScreenUpdating = False

'Add new worksheet as the last worksheet
Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.count))
'Rename the new worksheet
trg.Name = "Summary Report"

'Point to first worksheet in workbook
Set sht = wrk.Worksheets(1)
colCount = sht.Cells(1, 255).End(xlToLeft).Column

'Start loop
For Each sht In wrk.Worksheets
'If worksheet in loop is the last one, stop execution (it is Summary Report worksheet)
If sht.Index = wrk.Worksheets.count Then
Exit For
End If

'check to see if worksheet has any rows that meet the criteria
rowCount2 = sht.Cells(sht.Rows.count, "A").End(xlUp).Row
count = 0
For i = 4 To rowCount2
'if deadline date meets criteria
If sht.Cells(i, "E").Value >= Date And sht.Cells(i, "E").Value <= Date + 7 Then
count = count + 1
End If
Next i

'if there are qualifying rows, add them to the summary report
If count > 0 Then
'Skip a row in Summary Report worksheet
rowCount = trg.Cells(trg.Rows.count, "A").End(xlUp).Row

'Put sheet name in next available cell in column A of Summary Report
trg.Cells(rowCount + 3, "A").Value = sht.Name
trg.Cells(rowCount + 3, "A").Font.Color = RGB(255, 0, 0)
trg.Cells(rowCount + 3, "A").Font.Bold = True

'Retrieve column headers and put them in Summary Report (always in row 3 of worksheet)
colCount = sht.Cells(3, sht.Columns.count).End(xlToLeft).Column
sht.Cells(3, "A").Resize(, colCount).Copy trg.Cells(rowCount + 4, "A")

'cycle through rows in spreadsheet
For i = 4 To rowCount2
'if deadline date meets criteria
If sht.Cells(i, "E").Value >= Date And sht.Cells(i, "E").Value <= Date + 7 Then
'copy row to summary report
sht.Cells(i, "E").EntireRow.Copy trg.Cells(65536, 1).End(xlUp).Offset(1)

==>I think this is where the linkage should occur, but don't know the syntax

End If
Next i
End If
Next sht

'Resize the columns in Summary Report worksheet
With trg
Columns("A").ColumnWidth = 35
Columns("A").WrapText = True
Columns("B").ColumnWidth = 50
Columns("B").WrapText = True
Columns("C").ColumnWidth = 15
Columns("D").ColumnWidth = 15
Columns("E").ColumnWidth = 15
Columns("C").VerticalAlignment = xlTop
Columns("D").VerticalAlignment = xlTop
Columns("E").VerticalAlignment = xlTop
End With

'Screen updating should be activated
Application.ScreenUpdating = True
End Sub


Can anyone give me a clue as to how to do this?

[YellowLabPro]

Sanchez,
Can you post a copy of your book and what exactly you want it to do?

[Sanchaz]

Here's the workbook I've been using to debug my code.

The column I need to create links in is column E.

This is the scenario:

My manager runs the macro that creates the Summary Report worksheet.
He looks at the items in the report, then changes the date in column E.
The change he just made updates the corresponding cell on the source worksheet.

In other words, when the Summary Report is being created, a link needs to be created between the cell in column E of the source row and column E of the destination row. I hope I'm explaining it sufficiently.

[Charlize]

Just a thought and not tested :'put in column I the sheetname and - and $A$i
'i is the value of the actual row that was
'processed
'Later on you can use doubleclick event to check for
'column I
'If it has a value, copy the values of that row back
'to the original sheet by using split function to
'get the sheetname and the address of the cell
'You can even split up the address by using $ to
'get the column and the row
trg.Cells(65536, 1).End(xlUp).Offset(0, 8).Value = _
sht.Name & "-" & sht.Range("A" & i).Address