How to remove completed status rows from sheet 1 and list all completed statuses in sheet 2

[Jayne125]

Hi,

I have a workbook with headings on row 7 and goes from column A-O. My status column (Complete) is column J. I'm trying to get my workbook to remove all rows that are marked complete in sheet 1 and enter the multiple rows in to sheet 2. This will be a live book that will be used for some time so data will keep moving over to sheet 2, therefore I need all data to be listed and not just replace it each time. Can anyone help? Thank you in advance

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Jayne()
   With Sheets("Sheet1")
      .Range("A7:O7").AutoFilter 10, "Complete"
      With .AutoFilter.Range.Offset(1)
         .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
         .EntireRow.Delete
      End With
      .AutoFilterMode = False
   End With
End Sub

 

[Jayne125]

Thank you for this Fluff,

I do apologise in advance as i am new to this. I went to developer tab - visual basic - insert tab - module and pasted your VBA code but nothing happened? Am i doing something wrong?

Any help would be great, Thanks

 

[alansidman]

Standard Module
How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button