Copy, Move And Delete Row, Based On Cell Value
I have a spreadsheet (Sheet 1) listing current Work Orders with each work order occuping a seperate row; Column E lists the status of the work order, with the status being chosen from a drop down list.
I would like to have a macro that will copy the entire row and paste into (Sheet 2) when the status is changed to CLOSED, and clear the contents of the cells on Sheet 1.
The aim of this being of course to have all open work orders on sheet 1 and all closed orders on sheet 2.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Move Rows To Another Worksheet Based On A Cell Value In That Row
I currently have a consolidated worksheet (thanks Bill!) called " Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up? here are some additional information: The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.) The "status" column is at column 11.
View Replies!
View Related
Move Rows To Another Worksheet Based On A Cell Value In That Row ...
I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.
View Replies!
View Related
Automatically Move & Sort Row Based On Cell Value
I have a simple projectlist on sheet1 formatted like this: Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status. On sheet2 I have the same columnheaders. What I would like to do, is that if Status changes to "Done", * the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything * the now empty row on sheet1 should be deleted (so there're no empty rows) * the data on sheet2, should be sorted with D as first key and C as second. Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1. My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour? I only know small bits of VBA but my idea is this: ...
View Replies!
View Related
Move Rows To Worksheets Based On Cell Text In Row
I download our monthly bank transactions (.csv). I have a workbook with sheets named for each creditor or expense. I would like move each row, based on the specific word in a cell row, to the specific worksheet. Here are some typical transactions in the Description column: Some are specific: 1/7/2008ACH WEB-SINGLE 9085863 VONAGE AMERICA VONAGE “Moved to the Vonage worksheet.” Others are not so obvious: 2/1/2008CHECK CARD PURCHASE XXXXX4636 BOSTON MARKET #0450 GREENSBURG PA “Moved to the Eat Out worksheet.” 2/21/2008CHECK CARD PURCHASE XXXXX4636 ASPCAPS XXXXX0028 MD “Moved to the ASPCAPS worksheet.” 2/19/2007POS PURCHASE POS54309901 0014264 PITTSBURGH ST GREENSBURG PA “Moved to the Grocery worksheet.” I was able to find the following while during a Search: Move Cells Containing Specific Word In Column To New Sheet The question was answered, in addition to Dave Hawley, by Bill Rockenbach who inserted the following code - “Sub FindWord()” Option Explicit Sub FindWord() Dim Sentences Dim Word As String Dim i As Long Dim iWordPos As Integer Dim lRow As Long Dim sWord As String If this is a possibility for what I'm looking for, I’m not sure how to implement it into my situation.
View Replies!
View Related
Delete Row Based On Cell Below
I currently have a spreadsheet with circa 50,000 lines with info regarding our websites at work. In every row, column "A" contains a unique reference number.There eill either be 1 row for this reference number, or there will be 2 rows. there will never be more than 2 rows for each reference. I wanted some code to say that if theres only one row, then delete it but if the if the cell underneath it is the same then its ok. I tried the following but it deletes the row anyway, supposing the cell under it is the same or not Sub test() Dim icell icell = ActiveCell.Offset(1, 0).Value Range("A1").Select Do If ActiveCell.Value = icell Then ActiveCell.Offset(2, 0).Select Else ActiveCell.Rows.Delete End If Loop Until ActiveCell.Value = "" End Sub
View Replies!
View Related
Delete A Complete Row Based On Time Value In Cell
I have a spreadsheet with date and time values of the format "dd/mm/yyyy hh:mm" in column A followed by some other data in cells of that row. What I'd like to do is have a macro that will delete a complete row if the time value in column A lies between two times that I can specify in the macro (the dates are irrelvant)
View Replies!
View Related
Delete Entire Row Based On A Cell Color
I used this macro to find the duplicates in column B: Sub KryDups() ScreenUpdating = False FirstItem = ActiveCell.Value SecondItem = ActiveCell. Offset(1, 0).Value Offsetcount = 1 Do While ActiveCell <> "" If FirstItem = SecondItem Then ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0) Offsetcount = Offsetcount + 1 SecondItem = ActiveCell.Offset(Offsetcount, 0).Value Else ActiveCell.Offset(Offsetcount, 0).Select FirstItem = ActiveCell.Value SecondItem = ActiveCell.Offset(1, 0).Value Offsetcount = 1 End If Loop ScreenUpdating = True End Sub The duplucate cells are now red in color. (RGB(255, 0, 0)). How do I now code VB to delete the rows in column B where the cell color is red? Here is some of the code that I tried:................
View Replies!
View Related
Move Column Data To Row Then Delete Those Rows
We have a client that provided us with a text file that we imported into Excel. Rather than have the entire record in one row, they have the record in 4 rows. What I need a macro or something to do is move award 2 award 3 and award 4 into the same row and after doing this delete the other rows. Below is an idea how this looks. 1JohnSmithstreetcitystateZIPaward 1 2JohnSmithstreetcitystateZIPaward 2 3JohnSmithstreetcitystateZIPaward 3 4JohnSmithstreetcitystateZIPaward 4 5BillJonesstreet2city2state2ZIP2award 1 6BillJonesstreet2city2state2ZIP2award 2 7BillJonesstreet2city2state2ZIP2award 3 8BillJonesstreet2city2state2ZIP2award 4 What we need for our data to work is: 1JohnSmithstreetcitystateZIPaward 1award 2award 3award 4 2BillJonesstreet2city2state2ZIP2award 1award 2award 3award 4
View Replies!
View Related
Copy Row Based On A Cell Value
I have a spreadsheet that contains office names (Brookside, Blairsville, New Castle, etc) in Column A. The rows next to those show student names and test scores, etc. I'd like a formula to take each office and copy the data belonging to that office and paste it to a sheet for that office. For example: A B C Brookside Tom 82 Brookside Bill 90 New Castle Phil 80 I'd like to take Brookside's data and past it to a 'Brookside' sheet.
View Replies!
View Related
Move Row Based Off Criteria
What I am trying to do is if on the (Open Project Issues) tab column F says "Closed" it will move that row to a tab called (Closed Project Issues) tab. I was trying something like this: Sub testmove() Dim c As Long For c = ActiveSheet.UsedRange.Rows.Count To 2 Step -5 If Cells(c, 6) = "Closed" Rows(c).Cut End If Next c.......
View Replies!
View Related
Vba To Copy And Insert Row Based On Cell Value
vba to copy and insert row based on cell value I have a spreadsheet with serveral thousand lines. I need to add row(s) and fill-in values based on a value in anothe cell; for example, Based on the value in Column A, a row(s) needs to be added below the row and a count (value) to B needs to be inserted into the cell in Column B. This step needs to be repeated for all rows to the end of the sheet. I would want to copy and insert the entire row. LABELAB110 2 1
View Replies!
View Related
Copy Row From Sheet1 To Sheet Based On Cell Value
i'm trying do is have information from sheet1 copy to sheet2 upon selecting a value from a DV list. Based on my attached sample, in Column J, upon selected "Yes", I would like to get the information from A6:H6 copied to sheet2 in the same row. I've tried it with the "if" formula and writing a macro for it.
View Replies!
View Related
Copy/Cut Entire Row To Another Sheet Based On Cell Value
Based on the example and solutions from one of our friends post http://www.excelforum.com/excel-prog...ell-value.html. i want to know the code with the same data as posted in THAT example,changing the data slightly like adding "TODAY" & "TOMMORROW" as other key words which will be Cut/Copied as seperate groups one beneath the other. I had attached the worksheet with the actual data & the final Required format.
View Replies!
View Related
Copy/Move Rename Sheets Based On List
I have a list of names in sheet1, starting with cell A3. I would like to copy sheet3, insert it after sheet3 and rename the sheet to correspond to the names in the list. If i have 30 names I would like 30 sheets. If i add a name, I would like to repeat the copy, insert and rename steps for the extra names as i add them.
View Replies!
View Related
Insert Row On Sheet & Move Active Cell Row To It
I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time. Example: Sheet 1 – is current jobs and sheet 2 is old jobs. My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2. I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.
View Replies!
View Related
Move Cell Contents & Delete Rows
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
View Replies!
View Related
Move Cell Value To Last Used Row Of Another Workbook
I have recorded a Macro but cannot figure out how to edit it to accomplish my goal beyond the first use. I want to to Highlight a Row in Worksheet A, ( Amazon Open.xls ) - Run my Macro to Cut that Row -- then Open Worksheet B (Amazon Sold.xls) and Paste that row into the next unused Row. Then copy Cell L in that row to clipboard. Heres what I have recorded -
View Replies!
View Related
Delete A Row Based On Condition
I want to delete a row in excel based on a condition. The condition is as follows: I have few rows in the excel where the columns 7,12,13,23 are same and column 24 in one row has some number and in some other row the column 24 is blank with orange color filled(the colorindex for orange is 44). In this case I want the row with orange color filled in column 24 to be deleted.
View Replies!
View Related
Delete Row Based On Condition
i have an issue i need to delete entire row if a condition is met, i need syntax if possible to delete if a cell word "begins with" a certain letter ...in this case...letter A column is "O" must search threw entire row
View Replies!
View Related
Split Cell By Delimiter, Move To New Row
I'm prepping this file to be pushed through mail merge. The sheet has 25,213 rows. Column I contains a long string of character (for the purposes of my project the field is called Subjects) and $ used as delimiters (approx 70 or as few as 1). This column needs to be split apart by $ and the separate Subjects that result from splitting apart by $ needs to be placed on a separate line. Once each Subject is on a separate line, all of the Subjects need to be lined up in Column I, one under another (the obvious result at this point is GREATLY increasing the number of rows in the sheet). Next, all of the data that is contained in each Subject's originating row will be copied to each of newly created rows (without copying over the Subject contained in column I). After writing this all out, I realize (once again).
View Replies!
View Related
Split Cell Into Two And Move To Another Row And Columns
I have a report that I need to reformat where part of the information is moved from rows to columns. The report is broken up into "sections" as follows: Each “section” is a series of multiple rows and is broken down as follows: ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows LAST ROW: Contains the Rep Subtotal I need to spit out a report that contains the invoice data only (the middle part of the “section”). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.
View Replies!
View Related
Cut/Move Cell Values Down 1 Row
I got the following format of data which I need to copy the cell values to the next row if it's found. Columnl1 Row 1 00185 Row 2 Row 4 Row 5 00123 Row 6 what i want is to copy from row1 to row2 & row5 to row6. How am I going to code it in macro ?
View Replies!
View Related
Delete Row Based On Conditional Format
Im having some issues with some code Im working on. My goal is to delete the rows that do not have conditions met. Conditional formatting is used in 6 columns of my spreadsheet and the cell fill is to turn red when below .50 . This code is shown below. What I need to do now is look at the 6 columns where the conditions are applied, and delete any rows where the column cells are not red. To further clarify, if in row 1 column X:X does not meet criteria, but column AL:AL does, the row would not be deleted. However if none of the columns meet the criteria, the entire row is deleted.
View Replies!
View Related
Delete Entire Row Based On Selection
I have a worksheet where I have restricted the users from scrolling around beyond a certain range. Because of that the user is prevented from selecting an entire row. There are some cases where the user should be able to delete entire row or range of rows. How can I allow them to select say cells "C17:C35" [they would just hoghlight the range] and hit ctrl+d; which would trigger a macro and the macro in turn would delete all the rows in that selection [rows 17 through 35]? I know some basic codes for deleting entire rows, but I don't know how to read first and last row of the selection under Target property.
View Replies!
View Related
Delete Rows Based On Two Criteria Within Each Row
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...).
View Replies!
View Related
Delete Entire Row Based On A Condition
I have a problem deleting rows based on a condition, i didn't know how to use offset method. i'm using a table like this one : A B C 1 Main design1 FFR0 2 Extra design2 FFR0 3 Main design3 FFR0 4 Extra design4 FFR0 . . n Main designn FFR0 I want to delete entire row if "FFR0" in column C exists in a row beginning by "EXTRA" (column A)
View Replies!
View Related
Delete Row Based On Range Selection
Here is my ThisWorkbook.sheets("sheet1").Range("M").Select For Each cell In Selection If cell.Value = ThisWorkbook.Sheets("Sheet2").Range("A").Value Then cell.EntireRow.Delete End If Next cell Range("a1").Select End Sub I want to remove all rows in sheet 1 that contain any value found in Sheet2 A I using XL 2003.
View Replies!
View Related
Delete Row Based Upon Multiple Criteria
I am attempting to create a macro that will delete an entire row, based upon multiple criteria. The data looks something like this: -----Column A-----------Column B--- 1.-- Computer_X ------- Software_A 2.-- Computer_X ------- Software_B 3.-- Computer_X ------- None 4.-- Computer_Y ------- None 5.-- Computer_Z ------- Software_A 6.-- Computer_Z ------- None I would like to delete the entire row if: 1) Column B entry is "None" AND 2) Column B has entries other than "None" that match Column A So I do not want to delete a row with a unique name in Column A and "None" in Column B. I only want to delete the rows that have "None" in Column B, and multiple entries for the match in Column A. Using the data example above, Row 3 and 6 would be deleted. Row 4 would not be deleted, since Column A (Computer_Y) had only one row of data, with "None" entered in Column B.
View Replies!
View Related
Delete Row And Loop Based On Condition
i have a set of data as below and wish to delete ENTIRE ROW if cell is the same but keep the first entry when code comes to it..ie in the data set below excel would keep first row and delete 2nd,3rd,5th, 6,7,8 and then go to next unique identifier which would be AU0000LIFHB3 this would not be deleted as it is unique the would proceed to AU300GPTC011. this process would then stop when no data was available AU300AUS1019 AU300AUS1019 AU300AUS1019 AU0000LIFHB3 AU300AUS1019 AU300AUS1019 AU300AUS1019 AU300AUS1019 AU300AUS1050 AU300GPTC011 AU300GPTC011 AU300GPTC011 AU300GPTC011 AU300INTE018 AU0000LIFHB3 0 AU300SUNQ027
View Replies!
View Related
Delete Row Based On Input Box
I am trying to write a macro that asks which row in the active worksheet I want to delete. It asks for confirmation, "Do you really want to delete row" and whatever the user inputted into the input box. It will also color the row yellow so it is clear which row is to be deleted. After confirmation it will delete the whole row and shift all other rows up.
View Replies!
View Related
Move Data Based On Cell Values
I would like to know if the following problem can be solved using only Excel without any operator intervention. Can a formula be created? If yes I would appreciate it very much Problem: If B5 = X Move B6 thru K6 to B5 thru K5 And Move B7 thru K7 to B6 thru K6 And than Blank out B7 thru K7
View Replies!
View Related
Delete Row Based On Starting Character 1st Then Contains
I would like to create a macro that deletes all rows starting with an open parenthensis in column B. The contents of what is in parenthis varies from 2 letters up - so anything starting with a parenthis is sufficient criteria. After the initial deleting (mentioned above), would also like to delete (from column B again), rows containing specific, multiple phrases. None of the functions I have see so far will facilitate this...
View Replies!
View Related
Move Rows In Table Based On Cell Values
I need to CUT & PASTE records according a cell value. For example: Columns A2 to AC1000 there are some values. In each rows of AD2:AD1000 the values repeats with the result something like "YES", "NO", "N.A.", "LESS CHANCE". So, I need to shift (to another sheet) only the entire rows with values of "NO" and "N.A."
View Replies!
View Related
Move Range Of Cells Based On Cell Criteria
I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell. Sub MoveRangeOfCellsBasedOnCellCriteria() Dim myrange, cell As Range Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp)) For Each cell In myrange If IsNumeric(Left(cell.Value, 1)) _ Or Left(cell.Value, 5) = "UNIT " _ Or Left(cell.Value, 4) = "THE " _ Or Left(cell.Value, 5) = "FLAT " Then Else cell.Range("A1:C1").Select Selection.Cut cell.Offset(0, -1).Range("A1").Select ActiveSheet.Paste cell.Offset(1, 1).Range("A1").Select End If Next cell End Sub
View Replies!
View Related
Move Files To Dynamic Folders Based On Cell Value
I am not so sure this can be done through a macro, but here is what I want. I have a folder with say around 1000 excel workbooks. I need a macro to 1. Open each file in the directory specified 2. Read the contents of a particular cell, for ex, A1 3. Create a folder in the same directory with the value of A1 4. And, move the excel workbook itself to that folder. 5. Then it opens the next file 6. If the value of A1 is the same as that in the first workbook, it simply moves it to the already created folder, else it creates another folder with the new value of A1 and moves it there. Hope I am not too confusing. Searching the forums does not give me an exact picture of how it can be done. Note: I would also like an option where when I execute the macro, it should pop up with an option to select the folder in which the files are, since the files are not always saved in a static file path.
View Replies!
View Related
Copy And Paste Cells Into Another Cell If There Is An Empty Spot, If Not Move Down.
i need to have 6 or so cells copied and pasted into another sheet. (when a button is pressed) However before the data is pasted it checks if there is anything in the cell(s) that are being pasted into, if there is something then move down a line and paste. Here is the code i have already: Sub Order2Invoice() Sheets("OrderDatabase").Select Range("B65536").End(xlUp).Offset(1, 0).Select With ActiveCell .Value = Orderform!G5.Value .Offset(0, 1) = Orderform!E10.Value .Offset(0, 2) = Orderform!E11.Value .Offset(0, 3) = Orderform!E12.Value .Offset(0, 4) = Orderform!E13.Value .Offset(0, 5) = Orderform!E15.Value .Offset(0, 8) = Orderform!E15.Value End With Sheets("Invoice").Select End Sub
View Replies!
View Related
|
TRACKER
