S
Somewhere In Excel 2002
Joel,
Thanks for the reply....Could I send you my spreadsheet to show you what i
mean? Or if you prefer, you could send me an email at
(e-mail address removed) and I will send my spreadsheet for your
assistance.
Thanks
One method would be to use a worksheet change function. Then when the cell
is changed copy the data to the fisrt empty row on a summary worksheet.
to add code, right click tab at bottom of worksheet( normally sheet1).
Select view code. Paste code below on this sheet. Worksheet change
functions are place in VBA sheets pages (not module) and must be put on each
sheet you want the code to run on.
Sub worksheet_change(ByVal Target As Range)
For Each cell In Target
If (cell.Row = 5) And (cell.Column = 6) Then
EmptyRow = Sheets("Summary"). _
Cells(Rows.Count, "A").End(xlUp).Row
If (EmptyRow = 1) And _
IsEmpty(Sheets("Summary").Cells(1, "A")) Then
EmptyRow = 1
Else
EmptyRow = EmptyRow + 1
End If
cell.EntireRow.Copy Destination:= _
Sheets("Summary").Rows(EmptyRow & ":" & EmptyRow)
Exit For
End If
Next cell
End Sub
Thanks for the reply....Could I send you my spreadsheet to show you what i
mean? Or if you prefer, you could send me an email at
(e-mail address removed) and I will send my spreadsheet for your
assistance.
Thanks
One method would be to use a worksheet change function. Then when the cell
is changed copy the data to the fisrt empty row on a summary worksheet.
to add code, right click tab at bottom of worksheet( normally sheet1).
Select view code. Paste code below on this sheet. Worksheet change
functions are place in VBA sheets pages (not module) and must be put on each
sheet you want the code to run on.
Sub worksheet_change(ByVal Target As Range)
For Each cell In Target
If (cell.Row = 5) And (cell.Column = 6) Then
EmptyRow = Sheets("Summary"). _
Cells(Rows.Count, "A").End(xlUp).Row
If (EmptyRow = 1) And _
IsEmpty(Sheets("Summary").Cells(1, "A")) Then
EmptyRow = 1
Else
EmptyRow = EmptyRow + 1
End If
cell.EntireRow.Copy Destination:= _
Sheets("Summary").Rows(EmptyRow & ":" & EmptyRow)
Exit For
End If
Next cell
End Sub