Copying Rows to to another Sheet based on the column data?

[BhuvanRam]

Hi,


I had a requirement of copying the column data from sheet to another sheet in excel based on the condition. I am able to copy the single row but i am not able to do the same. I am new to excel VBA. so i planned for an algorithm as
1. Iterating rows using for loop
2. Checking the condition of the particular column value


but i am not acheive the code as above mentioned steps, and i dont know whether it hits the prformance. I had many rows nearly above 30k. so could anyone please suggest me the answer for this

I attached an sample excel sheet

[rscsmith]

Hi BhuvanRam

The following code should be pasted in to the Macro sheet for Sheet2. Do do this, open the VBA Editor and double click Sheet2 on the left hand side.

It will run every time that sheet is activated.

Private Sub Worksheet_Activate()
Dim rRow As Range, CurRow As Single
CurRow = 2
'Clear Sheet 2 to make room for the new data
ActiveSheet.UsedRange.ClearContents
'Do this for each row on Sheet1
For Each rRow In Sheets("Sheet1").UsedRange.Rows
 
 'It it's the same, or the header row, copy it
 If rRow.Cells(1, 3) = "Same" Or rRow.Row = 2 Then
   rRow.Copy Destination:=ActiveSheet.Cells(CurRow, 1)
   CurRow = CurRow + 1
   End If

 Next rRow
End Sub

Best regards, Rob.

[BhuvanRam]

Hi,

Thank you for the Answer. But the problem is its taking so much of time to copy , because i had 65 Columns and 35000 Rows nearly. Do we have any other way to do it? And is it possible to copy the rows by leaving one row blank as offset(2,1)?

thanks
bhuvan