Help Needed! Macro to pull if cell does not equal criteria

[boothy]

Hi All,

Hoping somebody can help with this as ive been tearing my hair out for over a week now:

Im working on two sheets within one workbook, call them "AA" and "RawAA".
Raw data will be put into "RawAA" and if Column K (Heading is "STATUS") does not equal "MATCHED", then i want it to paste the entire row into tab "AA", but I want this data to be pasted in column "I" (Heading is "Name") in tab AA (obviously offset by one for the heading). If this could be set up so that it just lists them in tab "AA" then that would be perfect

This would then also be repeated for "RawBB" and "BB" and also "RawCC" and "CC", but any help for first bit would be great as i will eventually be assigning each an individual macro button, i can navigate around the rest once I have an idea.


Heres a failed previous attempt for you to ponder (/laugh at)

Attempt 1:

Private Sub Workbook_Open()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, j As Long
Set s1 = Sheets("RawAA")
Set s2 = Sheets("AA")
N = s1.Cells(Rows.Count, "K").End(xlUp).Row
j = 1
For i = 1 To N
If s1.Cells(i, "K").Value <> "MATCHED" Then
Else
s1.Cells(i, "K").EntireRow.Copy s2.Cells(1, 9)
j = j + 1
End If
Next i
End Sub


Any help would be massively appreciated.

[Paul_Hossler]

This


s1.Cells(i, "K").EntireRow.Copy s2.Cells(1, 9)

should be this


s1.Cells(i, "K").EntireRow.Copy s2.Cells(j, 1)



Try this slightly cleaner version



Option Explicit
Private Sub Workbook_Open()
Dim s1 As Worksheet, s2 As Worksheet
Dim N As Long, i As Long, j As Long
Set s1 = Sheets("RawAA")
Set s2 = Sheets("AA")

N = s1.Cells(Rows.Count, "K").End(xlUp).Row

j = 1

For i = 1 To N
If s1.Cells(i, "K").Value = "MATCHED" Then
s1.Rows(i).Copy s2.Rows(j)
j = j + 1
End If
Next i
End Sub




BTW, there are faster ways using Filters, but if performance is not an issue, you might find this more maintainable

[boothy]

Hi Paul,

Thanks for that - will that address the issue of pasting the rows into column K of tab "AA", that was the main problem i was experiencing really.

Kind Regards,

[Paul_Hossler]

Probably not since you can't paste an .EntireRow starting in column 9




s1.Cells(i, "K").EntireRow.Copy s2.Cells(j, 9)


I think you still need the 'j' to move down a row though

I 'think' you're looking for something like this



Option Explicit

Private Sub Workbook_Open()
Dim s1 As Worksheet, s2 As Worksheet
Dim r As Range
Dim N As Long, i As Long, j As Long
Set s1 = Sheets("RawAA")
Set s2 = Sheets("AA")

N = s1.Cells(Rows.Count, "K").End(xlUp).Row

j = 1

For i = 1 To N
If s1.Cells(i, "K").Value = "MATCHED" Then
Set r = Range(s1.Cells(i, 1), s1.Cells(i, s1.Columns.Count).End(xlToLeft)) '****
r.Copy s2.Cells(j, 9)
j = j + 1
End If
Next I
End Sub



In the attachment, I commented out the real WB open and just used a regular Sub to test

The **** marked line Sets a Range beginning with the first cell in the I-th row of RawAA and ending with the last cell in that row with data (goes from the last column on the WS to the left)

Just this range (not the .EntireRow) gets copied to the next available AA row (the j-th) starting in column 9

[boothy]

Thanks very much for this Paul, ill give it a try when I'm back with my computer tomorrow.

If its any help, it wouldn't need to be the entire row copied from RawAA if K <> "Matched", Column A to X would suffice if that would make it any easier.

Cant thank you enough for your help so far.

Cheers

Craig

[boothy]

Hi Paul,

This is incredible. Just one little tweak needed, its pasting over the headings as its going into row 1 when its pasting into "AA", just needs an offset somehow.

Other than that its absolutely perfect, cant thank you enough

[boothy]

Fixed it! Thanks so much for this.