VBA Help: Inserting new rows with data

[ygsunilkumar]

Hi, Please find the excel sheet attachment. Actually i need the red color rows present in Sheet1 to be inserted exactly with the values in the Sheet2 based on UUID column. Please help me by providing the VBA Code.

Thanks in advance.

[lucas]

Is this task for Excel or MS Project?

[ygsunilkumar]

Task is for Excel

[lucas]

Moved to the Excel help forum.

[ygsunilkumar]

how to move into excel form. will i be having access to that?

[RolfJ]

You need to be a little bit more specific and explain what you mean by 'based in UUID column'.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If IsError(Application.Match(.Cells(i, "C").Value, Worksheets("Sheet2").Columns(3), 0)) Then

.Rows(i).Copy Worksheets("Sheet2").Range("A1").End(xlDown).Offset(1, 0)
End If
Next i
End With

End Sub
[/vba]

[ygsunilkumar]

Thanks for the code!!! code is just copying and inserting the red colored rows in the last rows of sheet2 but my task is to copy and paste the red colored rows exactly how it is in the Sheet1.

UUID column is just the concatenation of FNAME and CONTROL which is unqiue for the table.

Please find the excel attachement and refer Sheet3 which tells how the output should be after running the macro. It should exactly as Sheet1.

Thanks for you help!!!

[RolfJ]

Sheet3 of your latest attachment seems to me to be just another copy of Sheet1 of the workbook you attached to your original post. What we are looking for is an algorithm that instructs where to insert those additional rows. You said 'based on the UUID column'; what's missing is the 'how'.

[ygsunilkumar]

Please find my task in detail

1. Find the UUID Column Value of Sheet1 is present in UUID Column Value of Sheet2

2. If the value is found then check for any previous row/s are with red color in Sheet1

3. If yes then copy that red color row/s and paste it in the Sheet2

4. The red colored row/s should be pasted in Sheet2 how? - In Sheet1 for which UUID column value previous rows are in red color for that UUID value in Sheet2 should pasted.

for example in the excel attachment Sheet1 UUID column value is BCA, B and its previous row is in red color, now in Sheet2 the red color row should be pasted above BCA, B UUID column value in Sheet2.

for example in the excel attachment Sheet1 UUID column value is DDA, D and its previous rows is in red color, now in Sheet2 the red color row should be pasted above DDA, D UUID column value in Sheet2. Here it is two rows.

How many row/s are in red color, so that much of rows should be pasted.

[Bob Phillips]

[vba]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

Set sh = Worksheets("Sheet2")
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If IsError(Application.Match(.Cells(i, "C").Value, sh.Columns(3), 0)) Then

sh.Rows(i).Insert
.Rows(i).Copy sh.Range("A" & i)
End If
Next i
End With

End Sub
[/vba]

[ygsunilkumar]

Thanks for the code but it just inserting the rows (irrespective of colors) which are not there in Sheet2 but if you go through my detail task, red rows which are not there in the Sheet2 and is there in Sheet1 should be inserted. Code should consider the red colored row/s.

Please help me.

[Bob Phillips]

[VBA]

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet

Set sh = Worksheets("Sheet2")
With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To LastRow

If IsError(Application.Match(.Cells(i, "C").Value, sh.Columns(3), 0)) And _
.Cells(i, "c").Interior.ColorIndex = 3 Then

sh.Rows(i).Insert
.Rows(i).Copy sh.Range("A" & i)
End If
Next i
End With

End Sub
[/vba]

[ygsunilkumar]

Thank you very much for the code but slight changes has to be made. That is, the red rows which are inserting and pasting the data should be based on UUID column value, if the UUID column value of Sheet1 is present in Sheet2 and column value of previous rows are in red color then it has to paste it. Suppose, if some UUID column value is not there in the Sheet2 then their red rows should not be pasted.

[ygsunilkumar]

Hi.. Please help me, it will be very helpful if i get the code as early as possible. Thanks

[Bob Phillips]

I haven't replied becuase you seem to be saying the same as before, and the code I supplied does just that AFAICS.

[ygsunilkumar]

Hi, i am not saying samething and i think i didnot make it clear to you. Let me expain it in clear. The code just checking pasting red rows which are present in Sheet1 to Sheet2, even if UUID column values are not there in sheet2 also.

Actually my requirement is those red rows should be pasted only if the UUID column matches. To be more clear, if i have 8 rows(including red color) in the Sheet1 and 4 rows(excluding red color) in the Sheet2, now the code should match the non red color UUID column values and then paste red color in the sheet2. Suppose non colored UUID column value is not there in sheet Sheet2 then above rows which contains red color should not paste it in Sheet2.

Please let me know if you need any clarification. Thanks

[SamT]

ygsunilkumar

Suppose Sheet1, Row7 is red
and Sheet1 Row8, UUID Column.Value = "ME"
then Find Row on Sheet2 where UUID Column.value = "ME"
now Suppose that Sheet2 Row is 5
Paste Sheet1 Red Row to Sheet2 Row 4.

Is that what you want?

[SamT]

[VBA]Sub Copy_Red_Row_To_New_Sheet()
Dim Copy_Sheet as Worksheet
Set Copy_Sheet = "Sheet1"
Dim Paste_Sheet As Worksheet
Set PasteSheet = "Sheet2"
Dim Paste_Row As Range
Dim UUID_Column As Long
UUID_Column = 1 ' for Column A
Dim UUID_Value As String
Dim Color_Red as Long
Color_Red = 3
Dim SomeCell As Range
Set SomeCell = Cells(1, UUID_Column)
While Not(Copy_Sheet.SomeCell.Value = "") 'This assumes you have no empty Rows
' in your Copy_Sheet
If SomeCell.Interior.ColorIndex = Color_Red Then 'You can use "AND"
'here and save an "IF"
If SomeCell.offset(1,0).Interior.ColorIndex = Color_Red Then
GoTo CheckNextRow
Else UUID_Value = SomeCell.offset(1,0).Value
Set Paste_Row = PasteSheet.UUID_Column.Find(What:=UUID_Value, _
After:=Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If Paste_Row = Nothing Then GoTo CheckNextRow 'Did not find UUID_Value
Else Set SomeCell = SomeCell.Offset(1,0)
Paste_Sheet.Rows(Paste_Row).Insert
Copy_Sheet.Rows(SomeCell).Copy Paste_Sheet.Range(Paste_Row)
End If
End If
End If

CheckNextRow:
Next
End Sub[/VBA]

[ygsunilkumar]

Suppose Sheet1, Row7 is red
and Sheet1 Row8, UUID Column.Value = "ME"
then Find Row on Sheet2 where UUID Column.value = "ME"
now Suppose that Sheet2 Row is 5
Paste Sheet1 Red Row to Sheet2 Row 4.

Is that what you want?

Yes. Right.

But i am getting error when i run the code as "Compile Error: Type Mismatch". Please help.