Copy and Paste Row to another sheet IF Column E does not equal BAS or DPO

[Jeffea]

Hello,

I hope you are doing well. I have a question. I am looking for vba code that will place data from sheet 1 to sheet 2 (for example) if the value in column F equals 25411013 AND column E in the same row does not equal BAS or DPO.

Sample code I have so far:

LR = Cells(Rows.Count, "E").End(xlUp).Row
With Range("E2:F" & LR)
.AutoFilter
.AutoFilter Field:=1, Criteria1:="25411013"
.SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A2")
.AutoFilter
End With

The above code will simply place all rows with a value of 25411013 in it to the Sheet2 sheet. I am looking for code that will place all rows with a value of 25411013 on Sheet2 UNLESS the value of column E in the same row equals BAS or DPO.


I would greatly appreciate any help you could give me concerning this matter!!

 

[Jeffea]

Just tried it.. it is not working. I am trying to understand the logic of it. Is it suppose to delete all rows with a value of BAS unless a particular row has "DPO" or "A$$" in it? The BAS value will always be in column E.. while the "A$$" or "DPO" value will always be in column C.

 

[Jeffea]

Looking to write a code that will delete all rows with a value of BAS unless that particular row has a value of "A$$" or "DPO" in it. BAS value will always be in column E will the "A$$" and "DPO" values will always be in column C.

 

[Jeffea]

AlphaFrog,

I am sorry, I am kind of a beginner with VBA code and I would reallyappreciate some help with another problem I am having. I need code that will look forrows with a value of 25411013 or 25411033 and put them on Sheet2 UNLESS a valueof BAS is in the same row.<o></o>
<o></o>
I have written two sets of code for each. The first set isto take rows with a value of 25411013 and put on another worksheet as long asthere is not a value of BAS in the same row. Then I added some code to deleterows with a value of 25411013 from the source worksheet (sheet1 for example). Idid the same thing for 25411033.<o></o>
<o></o>
I am wondering if there is some code that I could use thatwould look for 25411013 OR 25411033 AND does not have a value of BAS in thesame row and put it on sheet2 for example?<o></o>
<o></o>
The problem I am having now is the rows with a data value of25411033 are overwriting the 25411013 row information on Sheet2 starting fromcell A2 down.<o></o>
<o> </o>
<o> </o>
MR = Cells(Rows.Count, "E").End(xlUp).Row<o></o>
WithRange("E1:F" & MR)<o></o>
.AutoFilter<o></o>
.AutoFilterField:=2, Criteria1:="25411013"<o></o>
.AutoFilterField:=1, Criteria1:="<>BAS"<o></o>
Range("E2:F" &MR).SpecialCells(xlCellTypeVisible).EntireRow.CopyDestination:=Sheets("FED 457B").Range("A2")<o></o>
.AutoFilter<o></o>
End With<o></o>
<o> </o>
Dim LastrowD As Long<o></o>
Application.ScreenUpdating = False<o></o>
LastrowD = [E65536].End(xlUp).Row<o></o>
For e = LastrowD To 1 Step -1<o></o>
If Cells(e, 6) = "25411013" Then Rows(e &":" & e).EntireRow.Delete<o></o>
Next e<o></o>
<o> </o>
MR1 = Cells(Rows.Count, "E").End(xlUp).Row<o></o>
WithRange("E1:F" & MR1)<o></o>
.AutoFilter<o></o>
.AutoFilterField:=2, Criteria1:="25411033"<o></o>
.AutoFilter Field:=1,Criteria1:="<>BAS"<o></o>
Range("E2:F" &MR1).SpecialCells(xlCellTypeVisible).EntireRow.CopyDestination:=Sheets("FED 457B").Range("A2")<o></o>
.AutoFilter<o></o>
End With<o></o>
<o> </o>
Dim LastrowL As Long<o></o>
Application.ScreenUpdating = False<o></o>
LastrowL = [E65536].End(xlUp).Row<o></o>
For e = LastrowL To 1 Step -1<o></o>
If Cells(e, 6) = "25411033" Then Rows(e &":" & e).EntireRow.Delete<o></o>
Next e<o></o>

 

[Michael M]

Duplicate post


http://www.mrexcel.com/forum/excel-...-overwriting-previous-data-put-worksheet.html

 

[Jeffea]

I could have worded my question better. After I thought about it some more I realized that I basically want the data for 25411033 to be put in the first empty cell on sheet 2 for example.. "which would be after the 25411013 data was copied on sheet 2."

 

[Jeffea]

AlphaFrog,

I have made some more progress concerning my latest question. I just need some help with the syntax for writing code to place data from one sheet to another sheet on the first blank row. I get an error message of "object not defined" concerning the code in BOLD.

Dim NextRow As Range
Set NextRow = Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
MR1 = Cells(Rows.Count, "E").End(xlUp).Row
With Range("E1:F" & MR1)
.AutoFilter
.AutoFilter Field:=2, Criteria1:="25411033"
.AutoFilter Field:=1, Criteria1:="<>BAS"
Range("E2:F" & MR1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet5").Range("A2:A & NextRow")
.AutoFilter
End With

 

[Jeffea]

AlphaFrog,

I got it. I found the code I needed. Thank you again for your help.

Here is the winning code! Just in case it may be helpful for someone else...
Dim NextRow As Range
Set NextRow = Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
MR1 = Cells(Rows.Count, "E").End(xlUp).Row
With Range("E1:F" & MR1)
.AutoFilter
.AutoFilter Field:=2, Criteria1:="25411033"
.AutoFilter Field:=1, Criteria1:="<>BAS"
Range("E2:F" & MR1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet5").Range("A" & Rows.Count).End(xlUp).Offset(1)
.AutoFilter
End With