I have a spread sheet I am using to combine two reports from a DB. One is an
Incident tracking and the other is Action Item tracking. I want to combine
all Incidents with the action item that is associated to it. I have an
action item id on both data sheets. I am new to excel programming, but have
found some helpful information here. This is what I have so far:

Private Sub CommandButton1_Click()
On Error GoTo Abort
Dim wb1 As Workbook


Dim rng1 As Range, rng2 As Range
Dim c As Range, cc As Range, ccc As Range, cccc As Range

Set wb1 = ThisWorkbook

Set rng1 = wb1.Sheets("Sheet1").Range("A2:A1000")
Set rng2 = wb1.Sheets("Data2").Range("K2:K1000")

For Each c In rng1
For Each cc In rng2
If c.Value = cc.Value And Len(c) 1 Then
Set ccc = c
Set cccc = wb1.Sheets("Data2").Range("A" & cc.Row & ":" & "J" &
cc.Row)
cccc.Copy
ccc.Offset(1, 6).PasteSpecial xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
Next
Next
Exit Sub
Abort:
msgbox "Error: " & Err.Description & ", " & Err.Source
End Sub

This works, but I need for it to insert a blank row. Now it is inserting
the information on the next line with other incident information. It also
seems to only paste the first AI associated with an incident. Any Ideas how
I can make this work?

Record a macro to insert a row, you can get the code from that.

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kevin" wrote in message ...
I have a spread sheet I am using to combine two reports from a DB. One is an
Incident tracking and the other is Action Item tracking. I want to combine
all Incidents with the action item that is associated to it. I have an
action item id on both data sheets. I am new to excel programming, but have
found some helpful information here. This is what I have so far:

Private Sub CommandButton1_Click()
On Error GoTo Abort
Dim wb1 As Workbook


Dim rng1 As Range, rng2 As Range
Dim c As Range, cc As Range, ccc As Range, cccc As Range

Set wb1 = ThisWorkbook

Set rng1 = wb1.Sheets("Sheet1").Range("A2:A1000")
Set rng2 = wb1.Sheets("Data2").Range("K2:K1000")

For Each c In rng1
For Each cc In rng2
If c.Value = cc.Value And Len(c) 1 Then
Set ccc = c
Set cccc = wb1.Sheets("Data2").Range("A" & cc.Row & ":" & "J" &
cc.Row)
cccc.Copy
ccc.Offset(1, 6).PasteSpecial xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
End If
Next
Next
Exit Sub
Abort:
msgbox "Error: " & Err.Description & ", " & Err.Source
End Sub

This works, but I need for it to insert a blank row. Now it is inserting
the information on the next line with other incident information. It also
seems to only paste the first AI associated with an incident. Any Ideas how
I can make this work?