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

J

Jeffea

New Member
Joined
Aug 13, 2014
Messages
41
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!!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Try this...

Code: 
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    [color=darkblue]With[/color] Range("E2:F" & LR)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="25411013"
        .AutoFilter Field:=1, Criteria1:="<>BAS", Operator:=xlAnd, Criteria2:="<>DPO"
        .SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A2")
        .AutoFilter
    [color=darkblue]End[/color] [color=darkblue]With[/color]
 
Upvote 0
AlphaFrog! Thank you so much! It works well... the code will copy the rows with a value of 25411013 to Sheet 2 IF there is not a corresponding value of BAS in the same row. However, the first row that is copied to sheet2 is a duplication of the first row on Sheet1.

So it will do what I want it to do.. except for copying whatever is in row 1 of sheet1 to sheet2 as well.
 
Upvote 0
You're welcome.

The first row of an auto-filtered range is considered a header row and is not filtered.

The code below excludes row 1 (header row) from being copied.

Code: 
    LR = Cells(Rows.Count, "E").End(xlUp).Row
    [color=darkblue]With[/color] Range("E[COLOR=#ff0000]1[/COLOR]:F" & LR)
        .AutoFilter
        .AutoFilter Field:=2, Criteria1:="25411013"
        .AutoFilter Field:=1, Criteria1:="<>BAS", Operator:=xlAnd, Criteria2:="<>DPO"
        [COLOR=#ff0000]Range("E2:F" & LR)[/COLOR].SpecialCells(xlCellTypeVisible).EntireRow.Copy Destination:=Sheets("Sheet2").Range("A2")
        .AutoFilter
    [color=darkblue]End[/color] [color=darkblue]With[/color]
 
Upvote 0
I am sorry... I have one more question. I am looking for the write syntax for deleting rows with a BAS source code from Sheet1 IF the row does not have the values of "DPO" or "A$$" in it.

Here is my sample code:


DR = Cells(Rows.Count, "E").End(xlUp).Row
With Range("E1:F" & DR)
.AutoFilter
.AutoFilter Field:=2, Criteria1:="BAS"
.AutoFilter Field:=1, Criteria1:="<>***", Operator:=xlAnd, Criteria2:="<>DPO"
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End With

I am having trouble getting this code to work for me.
 
Upvote 0
Thank you again! This works well.. the only issue I am having with it now is that the header of sheet 1 is cut out. The header of sheet 1 is copied to sheets 2 and 3.. which is fine. I just don't want to have the header row cut out of sheet1.
 
Upvote 0
AlphaFrog,

I found out how to fix the header being cut out of Sheet1... I am just working on trying to get the rows in sheet1 with a value of BAS to be deleted unless there is a value of "***" or "DPO" in the row.

Thanks again..
 
Upvote 0
The asterisk character is a wildcard.
Do you want to filter on "<>***" which is equivalent to any blank cell, or do you want to specifically filter "<>A$$" ?

Approximately how many rows are you filtering and deleting; 10s, 100s, 1000s?
 
Upvote 0
I want to filter on the letters "DPO" or a three letter word also used to refer to a donkey. The $$ were used to replace the letter S. Approximately 100's rows. The most may be a couple hundred.. should never be any where close to the 1000s.
 
Upvote 0
Change the $$ to SS

Code: 
    [COLOR=darkblue]Dim[/COLOR] r [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]For[/COLOR] r = Cells(Rows.Count, "E").End(xlUp).Row [COLOR=darkblue]To[/COLOR] 2 [COLOR=darkblue]Step[/COLOR] -1
        [COLOR=darkblue]If[/COLOR] (Range("E" & r).Value = "A[COLOR=#ff0000]$$[/COLOR]" [COLOR=darkblue]Or[/COLOR] Range("E" & r).Value = "DPO") And _
            Range("F" & r).Value = "BAS" [COLOR=darkblue]Then[/COLOR] Rows(r).Delete
    [COLOR=darkblue]Next[/COLOR] r
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Copy and paste data through multiple workbooks
Replies
2
Views
198
CDaviess
C
B
  • Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
480
Alex Blakenburg
A
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
182
DanteAmor
DanteAmor
Kaz09
  • Question
Copy rows of data from multiple worksheets to based on column equalling YES
Replies
0
Views
107
Kaz09
Kaz09
I
  • Question
Find duplicate column values across multiple workbooks, and get data row and set row data to new sheet
Replies
2
Views
163
itomedina25
I

Forum statistics

Threads
1,214,824
Messages
6,121,784
Members
449,049
Latest member
greyangel23

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top