filter and copy rows into another sheet

[revozero]

Hi there

first time poster, very new to VBA

What im looking to do it writing a VBA procedure (not a function) to do an extraction job, where you first would set the Y/N field for each record manually, once the set is complete with Y/N's i want to then be able to press an active X button of some sort which then moves the records which are marked with a Y into another sheet, but i need to be able to factor in events where some records might be changed to N and then need to be moved back, is this possible? if so how would i write this code?

Thanks

 

[My Aswer Is This]

Assuming your data is on Sheet(1)
Sheet(1) is always the sheet in the far left position on your tab bar

This script will copy your rows with "Y" in column "A" over to sheet (2)
And then it will delete all those rows from sheet (1)

Try this script and if it does what you want then I will write you another script to reverse the action if "N" is in column ("A") of sheet (2)

Sub If_Equals_Y()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Y" Then
            Rows(i).Copy Destination:=Sheets(2).Rows(Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    Next

''''Delete Area
Sheets(1).Activate
    For i = Lastrow To 1 Step -1
        If Cells(i, 1).Value = "Y" Then
            Rows(i).Delete
        End If
    Next

Application.ScreenUpdating = True
End Sub

 

[revozero]

Assuming your data is on Sheet(1)
Sheet(1) is always the sheet in the far left position on your tab bar

This script will copy your rows with "Y" in column "A" over to sheet (2)
And then it will delete all those rows from sheet (1)

Try this script and if it does what you want then I will write you another script to reverse the action if "N" is in column ("A") of sheet (2)

Sub If_Equals_Y()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Y" Then
            Rows(i).Copy Destination:=Sheets(2).Rows(Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    Next

''''Delete Area
Sheets(1).Activate
    For i = Lastrow To 1 Step -1
        If Cells(i, 1).Value = "Y" Then
            Rows(i).Delete
        End If
    Next

Application.ScreenUpdating = True
End Sub

Hi, thanks for this

i couldent get it to work, the data is going from sheet 3 to sheet 4, i did change the relevant lines of code but it didnt seem to work,

I did get this to work, but im not sure how to factor in the reverse where i will need to move data back, and it cuts the data rather than copying??

Sub CheckBox1_Click()

Dim LR As Long
Range("A11").EntireRow.Insert Shift:=xlDown
LR = Sheets("All Sites").Cells(Rows.Count, "D").End(xlUp).Row
LR1 = Sheets("Removed Sites").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("All Sites").Range("D11:D" & LR)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="Y", _
Operator:=xlOr, Criteria2:=""
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Removed Sites").Range("A5:D5" & LR1)
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Selection.Delete Shift:=xlUp
End Sub

 

[My Aswer Is This]

In your original script you did not indicate what sheet the data was in or what sheet you wanted the data moved to.
If you will tell me what sheet the data is in and what sheet you want it moved to then I will fix the script.
This is why when you ask questions you need to provide details

As far as reversing the script I mentioned in my original post I would write that script after we got this first part working. I'm not able to sort through other scripts you may have and getting them to work for you.

 

[revozero]

In your original script you did not indicate what sheet the data was in or what sheet you wanted the data moved to.
If you will tell me what sheet the data is in and what sheet you want it moved to then I will fix the script.
This is why when you ask questions you need to provide details

As far as reversing the script I mentioned in my original post I would write that script after we got this first part working. I'm not able to sort through other scripts you may have and getting them to work for you.

The data is in sheet 3 and it needs to go to sheet 4, thanks

 

[My Aswer Is This]

Try this:
And you should know sheet(3) is the sheet in the third position on your sheet tab bar sheets are numbered in the way they are positioned on the tab bar.
1,2,3,4

Sub If_Equals_Y()
Application.ScreenUpdating = False
Dim i As Integer
Dim Lastrow As Long
Dim Lastrowa As Long
Sheets(3).Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets(4).Cells(Rows.Count, "A").End(xlUp).Row + 1

    For i = 1 To Lastrow
        If Cells(i, 1).Value = "Y" Then
            Rows(i).Copy Destination:=Sheets(4).Rows(Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    Next

''''Delete Area
Sheets(3).Activate
    For i = Lastrow To 1 Step -1
        If Cells(i, 1).Value = "Y" Then
            Rows(i).Delete
        End If
    Next

Application.ScreenUpdating = True
End Sub

 

[revozero]

It didnt work im afraid, does this go into the ThisWorkbook object or Sheet 3 object, i copied it into the ThisWorkbook object and ran the macro, probably missing something obvious here

 

[My Aswer Is This]

This is a modular script which gets put in a module.
1.Right click on any sheet tab.
2. Choose View Code
3. On the menu choose "Insert"
4. Choose Module
5. Paste in the code
6. Then to run the macro
7. Close that window
8. On the ribbon choose "view"
9. Choose macro and choose the macro named "Sub If_Equals_Y"
10. If you want you can make a button to run the script or a keyboard shortcut.

 

[revozero]

Thanks for explaining that, appreciated

Followed those steps and nothing is happening im afriad

 

[My Aswer Is This]

Have you ever run macros before?
If not you need to save your workbook as:
Macros Enabled.
If your using Excel 2013 the file extension should be like this:
MyWorkBook.xlsm