# Copy Rows In Range Based On Criteria

Jul 14, 2006

which I have modified from one that I use before for sheets 1 & 3 (instead of 4&5)

But it isn't working! I have defined a dynamic range in sheet 4, which is the range I want to search (its named "search") should i be using this range in the formula somewhere?

Private Sub CommandButton1_Click()

Dim rngFind As Range

With Sheet4.UsedRange
Set rngFind = . Find(ComboBox1.Text, LookIn:=xlValues)
If Not rngFind Is Nothing Then
Do
rngFind.EntireRow.Copy Sheet5.Range("A" & Sheet5.Rows.Count).End(xlUp).Offset(0, 0)
Set rngFind = .FindNext(rngFind)
End If ..................

## Copy Rows From One Workbook To Another Based On Criteria

Aug 4, 2009

I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.

This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.

## Copy Rows Based On Multiple Criteria ....

Oct 31, 2006

I am trying to loop through a column and cut and copy cells containing one of ten or more criteria (text strings) to the next worksheet, which already exists.

The basic loop is set up, and I understand that an array for the multiple criteria is needed but am falling at this hurdle, as well as struggling with the routine to copy the row to the next available row on the next worksheet.

I have omitted to post my effort so far, as it's embarrassingly basic, but can anyone help with the code?

## VBA Code To Copy Rows To A New Worksheet Based On Criteria

Dec 17, 2008

I need a macro that will search data from a range of cells in one column for multiple criterias and them copy the entire rows to a new worksheet.

Example I have a list of group names

Network
Telcom
Help Desk
BA
Network

I only want to choose all Network and Telcom rows copy to another worksheet.

## Copy Rows To Another Worksheet Based Upon Multiple Criteria

Apr 8, 2009

I have seen all kinds of copying routines, but haven't been able to get what I want.

1. Copy Row contents from activeworksheet

2. The activeworksheet will have various row counts

3. The activeworksheet has row 1 as header.

4. The criteria to use is in the current worksheet columns P through Z

5. The criteria to look for is the word inspect. only the word inspect, not words that contain inspect (i.e. "inspection") should not be found.

6. The word could be in column P and in S, but needs to be only copied once, becuase it has meet the criteria, but it also could only be in c

7. Copying the row into a Sheet named "Inspection"

As a bonus would be cool to be able to copy certain cells or the entire row.

## Copy Rows Based On Criteria In Single Column

Jan 30, 2008

I have on sheet1 of my workbook a number of rows of data, from column A through Column G. The Data in column G either contains the text "Yes", or "No".

I need a macro that will look through each row in this sheet, and upon finding a "Yes" in the cell in the column G, will copy that entire row (columns A through G) into a new row in sheet2. Sheet2 currently contains data, so I'd like to move everything down by one row and paste this data at the top (row 6 is the first line of data on this sheet).

I'd then like to go back to sheet 1, and delete the row I've just copied. Then continue though the loop. The loop needs to stop when a blank row is discovered.

## Automatically Copy Rows Based On Criteria In Column

Jul 31, 2008

I'm not sure that I'll be able to explain this clearly, so I've attached an example. There are 3 columns - Issue, Action Group and Status. Many Issues belong to the same Action Group and others do not belong to any Action Group. The is also a Status summary section that has the total Issues and Actions associated with each status. For example the Status called Resolved could have 10 issues and 5 Actions (including blanks). The problem is that I can't figure out a way to get the total for the Actions. As I said this explanantion isn't great, so the attached sheet

## Copy Every Nth Range Based On Criteria

Feb 15, 2010

I am copying from cell A2 to C2, then A8 to C8, etc. if the cell in column "A" contains the text "Item" until the end of data in column A. Here is what I am attempting:

Sub MoveItem()
sText = "Item"
sText = LCase(sText)
iMax = Cells.SpecialCells(xlCellTypeLastCell).Row
For i1 = iMax To 1 Step -1
If InStr(1, LCase(Cells(i1, 1)), sText) <> 0 Then
Cells(i1, 1).Copy Destination:=Cells(i1, 3)
End If
Next i1
End Sub

The problem is the destination location. If I define it as a Range eg: Cells(i1, 1).Copy Destination:=Range("C2") The macro works, but does not iterate the destination cell.

## Copy Rows From Multiple Worksheets To New Condition Based On Criteria

Nov 20, 2012

I have multiple sheets with the same format.(all columns have same format) in column "A" i have a value lets say "B1-1".

What I need to do is to copy from all sheets the rows containing in column "A" the same criteria("B1-1") to a new sheet and skip the rows that have a blank cell in Column "A".

## Copy Paste Multiple Criteria Based Rows To New Sheet

May 23, 2008

I need to copy specific rows that have a specific value in colum B and Colum M to new worksheets.

So from attached screenshot , say for an example macro shud filter rows that have "BigPond" in coloum B ,"RG2" in column M & "INT" in column W and i dont want all cells in these rows , i only want copy cells under column C,AK,AL,AM (in this order) to my other excel sheet that is named "BigPond" and it should paste it starting from Row5?

I want to avoid recording a macro as it selects a range and i am expecting more new rows every week so if a record a macro the cell range for selection changes and i get incorrect result.

## Copy Paste Rows To Another Sheet Based On Column Criteria

Jan 17, 2007

I have a file that has two sheets, I have some formulas in the first sheet named "Data",What i would like to have is, If column G in the sheet Data is "Closed", then i want that particular row to be cut from the sheet " Data" and pated in to the Sheet "Done".

## Copy Range Based On Criteria That Changes Constantly

May 7, 2009

I am trying to copy a range of cells, based on a set of criteria that changes constantly.

In Column A, I have numerical values that are present every 10th cell. In columns B through D, I have various text data that corresponds to the number in Column A. So for example, in A50, I have the number 46975. In the range B50-D59, I have text data that goes with the number in A50.

Each day I run a few calculations and based on the number I get, I want to find the exact match in column A, then copy all the data in columns B through D that are related. So if my calculations result in 46975, I want to automatically copy over to another worksheet the range B50-D59.

## Copy Rows Based On Criteria, Paste As Values To New Sheet & Sort

Apr 15, 2008

I have spent a few days searching through forums but cannot find examples that i have been able to successfully adapt ( because they are to complicated for my limited knowledge). I have a workbook with 5 sheets, sheet1 (current), and sheet 4 (archive) are the important ones. I need a macro to

A) copy rows from "current" to "archive" ( to the 1st empty row) if column S of "current" contains "Closed" ( the word is generated by formula).

B) The paste needs to paste special values and number formats ( want to lose formula but not conditional formatting).

C) data sort "archives" based on col A - this puts the newly archived data into correct sequence.Data sort lowest number 1st

D) delete the copied rows from "current".

e) give me a count of how many rows it deleted, (I have a macro to insert rows so will run that manually to recreate the correct number of emtpy rows (with formula and formatting) to bring the current sheet back to usual size.

I tried modifying a macro by RPaulson (based on cells on one sheet to cells on another), to work with entire rows but couldn't get it to work.

Found that paste special uses PasteSpecial xlPasteValues, but , and thats about it.

## Copy Range & Paste Based On Date Criteria

Jan 22, 2008

I have 2 ranges: 1."Task info" (4x4 range)
2." Dates" (24x2 range)

I have one variable cell ref: "Date1" (a date)

Based on "Date1" I want to copy the value in the "task info" and Paste special value that is next to the corresponding date in "dates".

## Automatically Copy/Paste Data From Input Range Based On Criteria

Sep 4, 2006

I have a workbook with 2 - worksheets "DATA" and "LETTER".

The "DATA" sheet has columns "First Name" "Last Name" "Address"...etc.

The user can enter in either a "X" or a "x" in cells B6:B100 on the "DATA" worksheet.

After the user enters a X or x and hits the macro button I need it to copy certain cells from "DATA" worksheet to "LETTER" worksheet. I have listed a example below.

In this example if a "X" or "x" is entered into B6 on "DATA" worksheet and the macro button is hit, then: ....

## VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

## Copy Rows To Sheet Based On Cell Value In Range

Aug 16, 2007

a macro button on my excel sheet that should do the following.

i have a range (Ex: E3:E46) which is being continuously filled, i need to transfer the information to another sheet depending on column "E", (Ex: if "E3" = "A" paste row to sheet2 if = "B" paste row to sheet3) and so on each row could have another string in column "E".

after the paste is done it should clear the range making place for new entries, that should also be copied finding the next empty cell (it shouldn't delete the old entries)

## Copy Rows To New Sheet Based On Date Range Selected On A Form

May 8, 2009

This spreadsheet must perform calculations for every line since new items are added every day, so VBA is probably better than copying formulas down every line of the spreadsheet:

With the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.

Also, I need to keep a total of all items by month as well as a monthly and annual average of the Total Item Value on the FY09 tab. This will eventually produce another sheet when a button is pressed to submit as a report. I think part of this answer is in using the MONTH(serial_number) function but I can only get this to work for a single cell. I need to search the entire Distribution 'D' column, match all the months to the FY09 tab to the respective month, and calculate the totals and averages. I think SUMIF may also be needed as well but need the MONTH(serial_number)to work first. If there is a way to code all of this in VBA that would be fine as well.

I have a pivot table on Sheet1 where I am trying to get the totals and averages described above but I am not sure it can do what I need. In column 'B' I need the total number of each item as well as the total number of all items. I tried various formats and adding the totals from the Totals tab but I have not figured it out.

## Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

## Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete

End If
Next Row_Counter
End Sub

## SUM Of Values In 1 Range Based On Specific Criteria In Another Range

Jan 31, 2009

I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against \$ Invoiced and \$ Committed (i,e worked but not actually invoiced against yet).

Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column.

This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.

## Copy Rows By Criteria In New Workbooks

Jan 26, 2010

I have a spreadsheet (see atched for example data). Using vba I would like to seperate the lines of data into new workbooks depending on their company code.
I.e all lines of data starting with '6002' would in a new workbook called 6002.xls.

I receive this data on a monthly basis and there are diffferent amounts of data per company each time.

## Copy Rows Matching Criteria

Jan 3, 2007

setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.

Heres the ....

## Copy All Rows With Criteria Match

Dec 29, 2007

I am trying to write a code to copy rows from sheet 1 to sheet 3 if cell of column A "account #" on sheet 1 are matching cell of column A (account #) on Sheet 2. If multiple entries find on sheet 1, (e.g. acct # 101c shows twice) copy all matched rows to sheet 3 as well.

## Macro To Copy Rows Based On Moving Date And Paste Rows Into Identical Sheet

Jan 28, 2014

I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)

e.g. 28/1/2014
28/1/2014
28/1/2014

I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.

## Cut Rows Based On Criteria?

Jan 19, 2014

There are two workseets involved. Inventory is the main and Pending is the second. There are 28 columns of data in each that match based on row 1 headers. I need to figure out how to cut all the rows with "Pend" listed in Inventory column Z to one row below the data in column A of the Pending worksheet. The data can be dates and also notes but no matter what is in column Z it must be cut and moved.

## Copy Rows Into Appropriate Worksheets Meeting Criteria

Mar 12, 2009

I have a worksheet that contains 2 Rn managers per 3 Site and 2 TSR managers per 3 Site.
I want to be able to sort these managers by Site into 6 tabs: 3 RN tabs and 3 TSR tabs.
I want to keep the original worksheet as is and copy the appropriate rows to the designated worksheets labeled SACRN SACTSR VALRN VALTSR and so on. The data range is A:M

The columns of value is A (JOB) and B (SITE) and here is the sample of the data

JOB SITE TEAM
RN SITE SAC GASTON SUSAN TEAM SAC BERNAL
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE SAC GASTON SUSAN TEAM SAC CASTILLO
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE VAL RANADA TERESA TEAM VAL RINCON
RN SITE SAC MCINNIS JOHN TEAM SAC SMITH
RN SITE SAC MCINNIS JOHN TEAM SAC LEMON
RN SITE SAC MCINNIS JOHN TEAM SAC JACK
TSR SITE SAC GRECO DAVE M TEAM SAC KEN
TSR SITE SAC GRECO DAVE M TEAM SAC ROGERS
TSR SITE SAC GRECO DAVE M TEAM SAC LOPEZ
TSR SITE SAC SHAVER KELLY TEAM SAC JONES
TSR SITE VAL FRANCIS TEAM SAC YANG

## Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

## Delete Rows Based On Two Criteria Within Each Row

Sep 24, 2009

I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool.

The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error).

The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...).

What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as:
"T" "["
"T" "#"
"T" "@"
"T" "]"

The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ).

I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).

## Hide Rows Based On A Criteria

Oct 6, 2009

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.