Very Basic VBA help needed urgently!

[munshi1111]

Hello all,

When it comes to VBA in excel, I will be the first to admit that I am a noob. I have come across a problem where I need help, urgently. So here is the situation:

I have three tabs (Orders, Logs, Completed) which contain data.
"Logs" has 7 columns (A-G)
Column G in "Logs" is doing a vlookup to find all the "y" values in "Orders" tab to show which orders a completed.

I need to find a code to cut and paste the rows of all "y" values I get in the "Logs" tab into the "Completed" tab. This is where I'm having an issue because I am getting these "y" values based on a vlookup.

After the row is moved, I need that empty row in "Logs" tab deleted. Any help??

I know this may come off as confusing, so let me know if I can make it easier to understand.

 

[well2ant]

Hi Munshi1111,

May not be what you're after but I've had similar issues when trying to collate data based on a particular column, to extract it into a different worksheet.

Basically, if you can insert a new column H (it will be hidden when we finish) and place a formula in column H

=ROW()

When you hide Column H, users will not see that it's there. But now you have a unique identifier to retrieve when you perform your VLOOKUP.

i.e. =VLOOKUP("y",$G$1:$H$999,2,FALSE) will give you the Excel Row number containing that record.


I'll have a look through some of my other projects, I may have some VBA that does something similar as well.

 

[MARK858]

Possibly...

Sub Filterit()
    With Sheets("Logs").Range("A1:G" & Sheets("Logs").Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=7, Criteria1:="y"

        On Error Resume Next
        
        With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .EntireRow.Delete
        End With
        
        On Error GoTo 0
        .AutoFilter

    End With
End Sub

 

[munshi1111]

When I tried to run this, it worked fine for the first time. I changed the value to "y" again but this time the row did not delete after moving to the other sheet and got empty rows so the cells didn't move up. I got really excited when it worked the first time but unfortunately these problems came about.

 

[MARK858]

Are you sure the row didn't delete as there is no reason I can think of why it didn't if the correct row copied to the other sheet and the code deletes not clears

Are you sure that what remains isn't a row that is formulas and recalculating? if you are sure then you need to work out what the difference is between your old data and the new before we can give you a possible solution.

 

[MARK858]

Actually look at the question in the message box when you run the code below, check it, click ok and then post back whether there are any yellow cells left.

Sub Filterit()
    With Sheets("Logs").Range("A1:G" & Sheets("Logs").Range("A" & Rows.Count).End(xlUp).Row)
        .AutoFilter Field:=7, Criteria1:="y"

        On Error Resume Next
        
        With .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)
            .Copy Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)
            .Interior.ColorIndex = 6
            MsgBox "Are the correct rows highlighted?"
            .EntireRow.Delete
        End With
        
        On Error GoTo 0
        .AutoFilter

    End With
End Sub