Macro to copy the records only which status shows Completed

S

scorpio_nml

New Member
Joined
Mar 24, 2008
Messages
15
Hi all.
My requirement goes like this
I have one workbook with five different columns values. 1. Project Title 2. Project ID 3. P_Family 4. Status 5. Initiator.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> </o:p>
Under P_Family column have team name called AP-Americas, AP-Asia, Finance and GP for these teams I have certain records which has the status of In progress, Completed.
I would like to copy only the Status Completed records to the other sheet.
<o:p> </o:p>
Example is as below
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto -27pt; WIDTH: 519.2pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=692 border=0><TBODY><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>Project Title<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>Project ID<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>P_Family<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Initiator<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=bottom width=92>Status<o:p></o:p>
</TD></TR><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>Activities Re- definition for butterfly & Printer – GSSC Scanning<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>GSC-ITK-0010774<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>AP Asia<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Poornalingam Sukumar<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt" vAlign=bottom width=92>Completed<o:p></o:p>
</TD></TR><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>ANSI Quality Control Method<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>GSC-ITK-0010416<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>GP<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Venkatesh Devachandiran<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=92>Completed<o:p></o:p>
</TD></TR><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>Ensure Availability of Sufficient Details in VM form<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>GSC-ITK-0010762<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>Finance<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Deenadayalan Gopalan<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=92>In Progress<o:p></o:p>
</TD></TR><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 4"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>AP America & Asia regions– Difficulties in tracking the Soft / Hard copy invoices<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>GSC-ITK-0010750<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>AP Asia<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Sukumar Yadav<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=92>In Progress<o:p></o:p>
</TD></TR><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 5"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>Auto matching in Singapore Sites<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>GSC-ITK-0010794<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>AP Asia<o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Annamalai Veerappan<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=92>In Progress<o:p></o:p>
</TD></TR><TR style="HEIGHT: 20.65pt; mso-yfti-irow: 6; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: black 1pt solid; WIDTH: 173.1pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid black .5pt; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=231>Automated Less Than 10K report (vendor due amount )<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 88.3pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=118>GSC-ITK-0011414<o:p></o:p>
</TD><TD style="BORDER-RIGHT: black 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 98.35pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt; mso-border-right-alt: solid black .5pt" vAlign=bottom width=131>AP <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:eek:ffice:smarttags" /><st1:country-region w:st="on"><st1:place w:st="on">Americas</st1:place></st1:country-region><o:p></o:p>
</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 90.7pt; PADDING-TOP: 0in; BORDER-BOTTOM: black 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-bottom-alt: solid black .5pt" vAlign=bottom width=121>Karthikeyan Perumal<o:p></o:p>
</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ece9d8; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 68.75pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 20.65pt; BACKGROUND-COLOR: transparent; mso-border-left-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom width=92>In Progress<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
<o:p> </o:p>
Thanks in advance!
Nirmala.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
try this
Code: 
Sub MoveCompleted()
'
'

    Range("A1").AutoFilter Field:=5, Criteria1:="Completed"
    Cells.Find(What:="Completed", LookAt:=xlPart).Activate
    LR = Cells(65536, 1).End(xlUp).Row ' last row with filtered data
    SR = ActiveCell.Address ' cell found with "completed"
    SRR = ActiveCell.Row ' row number of first found "completed"
    Range(SR).SpecialCells(xlCellTypeVisible).Select
    Range(Cells(SRR, 1), Cells(LR, 5)).Copy
    Sheets("Completed").Select
    LR2 = Cells(65536, 1).End(xlUp).Row ' last row on completed sheet
    Range(Cells(LR2 + 1, 1)).Paste
    Range("A1").Select
    Sheets("Sheet3").Select
    Application.CutCopyMode = False
    Range("A1").AutoFilter ' turn off auto filter
    Range("A1").Select
End Sub
 
Upvote 0
oops I didn't test and found I miss some things. Here is the revised

Code: 
Sub MoveCompleted()
'
'
Dim SName As String
SName = "Completed"
LR2 = Sheets("Completed").Cells(65536, 1).End(xlUp).Row ' last row on completed sheet

    Range("A1").AutoFilter Field:=5, Criteria1:="Completed"
    Cells.Find(What:="Completed", LookAt:=xlPart).Activate
    LR = Cells(65536, 1).End(xlUp).Row ' last row with filtered data
    SR = ActiveCell.Address ' cell found with "completed"
    SRR = ActiveCell.Row ' row number of first found "completed"
    Range(Cells(SRR, 1), Cells(LR, 5)).SpecialCells(xlCellTypeVisible).Select
    Range(Cells(SRR, 1), Cells(LR, 5)).Copy Destination:=Worksheets(SName).Cells(LR2 + 1, 1)
    Application.CutCopyMode = False
    Range("A1").AutoFilter ' turn off auto filter
    Range("A1").Select
End Sub
 
Upvote 0
Hi, thanks a lot for the macro...
I have another creteria, if i want to copy records in P_family AP ASIA and status Completed records. how do i do this?

Thanks in advance,
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
345
VBAEXCELNew
V
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
867
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
624
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,216,168
Messages
6,129,268
Members
449,497
Latest member
The Wamp

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top