Copy data from one sheet to another in Excel
 

Dear All,
I've a excel with data. I've marked those data in 2 different color(Say Red
and Yellow). Now i would like to move data which are marked in Red Color to
another sheet in the same workbook.
Actually i'm comparing data from 2 sheets and i'm marking differed data in
Red color. Or is there any other way while comparing data itself, moving to
another sheet?

Thanks in advance,
Prabu

Copy data from one sheet to another in Excel
 

Use this macro:
Function is_it_red(i As Long) As Boolean
is_it_red = False
For j = 1 To Columns.count
If Cells(i, j).Interior.ColorIndex = 3 Then
is_it_red = True
Exit Function
End If
Next
End Function


Sub colorcopier()
Dim i As Long
k = 1
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For i = 1 To nLastRow
If is_it_red(i) Then
Set rc = Cells(i, 1).EntireRow
Set rd = Sheets("Copy If Red #2").Cells(k, 1)
rc.Copy rd
k = k + 1
End If
Next
End Sub

Someone on this DG shared this with me a while back...now I am sharing it
with you...


Regards,
Ryan---

--
RyGuy


"Prabu" wrote:

Dear All,
I've a excel with data. I've marked those data in 2 different color(Say Red
and Yellow). Now i would like to move data which are marked in Red Color to
another sheet in the same workbook.
Actually i'm comparing data from 2 sheets and i'm marking differed data in
Red color. Or is there any other way while comparing data itself, moving to
another sheet?

Thanks in advance,
Prabu

Copy data from one sheet to another in Excel
 

Thanks Ryan.. But i'm getting Run-time error '9': subscript out of range
error from this code.

"ryguy7272" wrote:

Use this macro:
Function is_it_red(i As Long) As Boolean
is_it_red = False
For j = 1 To Columns.count
If Cells(i, j).Interior.ColorIndex = 3 Then
is_it_red = True
Exit Function
End If
Next
End Function


Sub colorcopier()
Dim i As Long
k = 1
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For i = 1 To nLastRow
If is_it_red(i) Then
Set rc = Cells(i, 1).EntireRow
Set rd = Sheets("Copy If Red #2").Cells(k, 1)
rc.Copy rd
k = k + 1
End If
Next
End Sub

Someone on this DG shared this with me a while back...now I am sharing it
with you...


Regards,
Ryan---

--
RyGuy


"Prabu" wrote:

Dear All,
I've a excel with data. I've marked those data in 2 different color(Say Red
and Yellow). Now i would like to move data which are marked in Red Color to
another sheet in the same workbook.
Actually i'm comparing data from 2 sheets and i'm marking differed data in
Red color. Or is there any other way while comparing data itself, moving to
another sheet?

Thanks in advance,
Prabu

Copy data from one sheet to another in Excel
 

kindly modify the following to
Set rd = Sheets("Copy").Cells(k, 1)

thanks
ramesh


Function is_it_red(i As Long) As Boolean
is_it_red = False
For j = 1 To Columns.Count
If Cells(i, j).Interior.ColorIndex = 3 Then
is_it_red = True
Exit Function
End If
Next
End Function


Sub colorcopier()
Dim i As Long
k = 1
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
For i = 1 To nLastRow
If is_it_red(i) Then
Set rc = Cells(i, 1).EntireRow
Set rd = Sheets("Copy").Cells(k, 1)
rc.Copy rd
k = k + 1
End If
Next
End Sub

*** Sent via Developersdex http://www.developersdex.com ***