PDA

View Full Version : VBA Help: Inserting new rows with data



ygsunilkumar
01-06-2010, 01:48 AM
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
01-06-2010, 01:21 PM
Is this task for Excel or MS Project?

ygsunilkumar
01-07-2010, 12:12 AM
Task is for Excel

lucas
01-07-2010, 01:01 AM
Moved to the Excel help forum.

ygsunilkumar
01-07-2010, 01:17 AM
how to move into excel form. will i be having access to that?

RolfJ
01-07-2010, 09:14 AM
You need to be a little bit more specific and explain what you mean by 'based in UUID column'.

Bob Phillips
01-07-2010, 11:16 AM
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

ygsunilkumar
01-07-2010, 07:25 PM
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
01-07-2010, 08:05 PM
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
01-07-2010, 10:13 PM
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
01-08-2010, 03:14 AM
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

ygsunilkumar
01-09-2010, 01:06 AM
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
01-09-2010, 01:14 PM
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

ygsunilkumar
01-09-2010, 08:04 PM
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
01-14-2010, 07:07 PM
Hi.. Please help me, it will be very helpful if i get the code as early as possible. Thanks

Bob Phillips
01-15-2010, 02:42 AM
I haven't replied becuase you seem to be saying the same as before, and the code I supplied does just that AFAICS.

ygsunilkumar
01-15-2010, 07:36 PM
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
01-17-2010, 09:58 PM
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
01-17-2010, 11:02 PM
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

ygsunilkumar
01-19-2010, 11:03 PM
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.

Bob Phillips
01-20-2010, 02:49 AM
No guarantees here as that code was riddled with compilation and logic errors, but I have made it compile error free, so you can test it



Sub Copy_Red_Row_To_New_Sheet()
Dim Copy_Sheet As Worksheet
Set Copy_Sheet = Worksheets("Sheet1")
Dim Paste_Sheet As Worksheet
Set PasteSheet = Worksheets("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 = Copy_Sheet.Cells(1, UUID_Column)
Do While Not (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 Is 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:
Loop
End Sub

ygsunilkumar
01-20-2010, 07:07 PM
Hi..This code is not working actually the sheets gets not responding for more than 15 minutes when i run the code.

Please help.

SamT
01-22-2010, 10:18 AM
Thanks XLD,
I knew my example was faulty. I could not open his file. I only offered it as an idea spark.

Bob Phillips
01-22-2010, 11:42 AM
Thanks XLD,
I knew my example was faulty. I could not open his file. I only offered it as an idea spark.

Sam, perhaps you can help the OP to get it runiing now :yes I didn't look to hard at what it was doing, so you are far better placed to help than I.

ygsunilkumar
01-23-2010, 06:34 AM
Hi, Please any one help. It will be very helpful if i get the code as early as possible. Thanks

ygsunilkumar
01-28-2010, 07:19 PM
Hi.. Please any one help

ygsunilkumar
02-08-2010, 06:10 PM
Hi..I think no one wants to help me?????