VBA Code in Excel is Overwriting Previous Data Put on Worksheet

[Jeffea]

Hello,

I am kind of a beginner with VBA code and I would really appreciate some help with a problem I am having. I need code that will look for rows with a value of 25411013 or 25411033 and put them on Sheet2 UNLESS a value of BAS is in the same row.

I have written two sets of code for each. The first set is to take rows with a value of 25411013 and put on another worksheet as long as there is not a value of BAS in the same row. Then I added some code to delete rows with a value of 25411013 from the source worksheet (sheet1 for example). I did the same thing for 25411033.

I am wondering if there is some code that I could use that would look for 25411013 OR 25411033 AND does not have a value of BAS in the same row and put it on sheet2 for example?

The problem I am having now is the rows with a data value of 25411033 are overwriting the 25411013 row information on Sheet2 starting from cell A2 down.


Sample Code:

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 1Step -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>
.AutoFilterField:=1, Criteria1:="<>BAS"<o></o>
Range("E2:F" & MR1).SpecialCells(xlCellTypeVisible).EntireRow.CopyDestination:=Sheets("FED 457B").Range("A2")<o></o>

 

[Michael M]

Your 2nd code is a mirror of the 1st..
Is this what you are after... UNTESTED

Sub MM1()
Dim MR As Long
MR = Cells(Rows.Count, "E").End(xlUp).Row
    With Range("E1:F" & MR)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="25411013"
        .AutoFilter Field:=1, Criteria1:="<>BAS"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("FED 457B").Range("A2")
        .SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .AutoFilter
    End With
End Sub

 

[Jeffea]

Hello.. thank you for your help. I will try your code shortly. 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]

Thank you Michael.. the code you gave me worked. 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]

Michael...

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