On Oct 14, 3:06*pm, joel wrote:
Create a new sheet called Summary and run this code. *I'm using columns
E and R from the two source sheets.
Private Sub cmdUpdate_Click()
Dim c1 As Range
Dim RowCount As Long
Dim NewRow As Long
Dim LastCol As Long
Dim NewCol As Long
Set Sumsht = Sheets("Summary")
NewRow = 2
For Each sht In Sheets
If sht.Name < Sumsht.Name Then
With sht
RowCount = 2
Do While .Range("E" & RowCount) < ""
Index = .Range("E" & RowCount)
Data = .Range("R" & RowCount)
With Sumsht
'search if Index already exists
Set c = .Columns("A").Find(what:=Index, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'add data to column B
.Range("A" & NewRow) = Index
.Range("B" & NewRow) = Data
NewRow = NewRow + 1
Else
'find last column used
LastCol = .Cells(c.Row,
Columns.Count).End(xlToLeft).Column
NewCol = LastCol + 1
.Cells(c.Row, LastCol) = Data
End If
RowCount = RowCount + 1
End With
Loop
End With
End If
Next sht
End Sub
--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=144045
Hi joel ! Thanks for your input.
Unfortunately when I try to run this code I get a message :
Compile error
Can't assign to read only property