Copy & Paste

[khalid79m]

I need help in writing a macro that will

on sheet1 filter on range named CHECK for Complete,

then on the filtered data cut it and paste on sheet 2 in the next avaliable row using column a to find then next available row.

Important please note on sheet1 row 1 and 2 are headers!

I have this process in placed but it involves using another sheet and mulitple filters, i need to stream line... any help would be appreciated:dunno

[lucas]

What are you filtering for? Can you check that column for that item and cut and paste if?

[khalid79m]

Each row of data from 3 to 65536 potenital may hold data about a Customer Call. So 1row = 1call... I want to filter on the range "CHECK" for "Complete" and the complete results need to be cut and pasted elsewhere.. does that make sense?

[lucas]

So what column would you be looking for the word "complete"

[khalid79m]

The column is AV3 & lastrow , (it saved as a named range called CHECK).

I hope that helps.

[georgiboy]

Something like this...

Sub Macro()
Dim rCell As Range

For Each rCell In Sheet1.Range("CHECK").Cells

If UCase(rCell.Value) = "COMPLETE" Then
rCell.EntireRow.Cut Sheet2.Range("A" & Sheet2.Range("A" & Rows.Count).End(xlUp).Row + 1)
End If

Next

End Sub

Edit: You may want to swap "A" for "AV" so it populates sheet2 fully

Hope this helps

[lucas]

This should work....it worked for me:
Option Explicit
Sub Main()

Dim i As Long

For i = TargetRow(ActiveSheet, 1) - 1 To 2 Step -1
If ActiveSheet.Cells(i, 48).Value = "complete" Then
ActiveSheet.Rows(i).Copy Sheets("Sheet2").Cells(TargetRow(Sheets("Sheet2"), 1), 1)
ActiveSheet.Rows(i).Delete
End If
Next i

End Sub

Function TargetRow(ByRef ws As Worksheet, ByVal col As Long) As Long
'returns index of first empty row from bottom of sheet
'requires worksheet object and column index
TargetRow = ws.Cells(Rows.Count, col).End(xlUp).Row
If IsEmpty(ws.Cells(TargetRow, col)) Then
'in this case the column is empty
'change targetrow to 2 to move the rows starting on the second row
TargetRow = 3
Else
TargetRow = TargetRow + 1
End If
End Function

[khalid79m]

Hi Gorgieboy,

what do you mean by

Edit: You may want to swap "A" for "AV" so it populates sheet2 fully?

Your code works fine by the way , exactly what I need as it uses the named range.

Thanks for the help lucas really appreciate it.