Copy/Move Row When Cell Meets Condition
I'm setting up a spreadsheet for work and i have a question. I'm not familiar with the formulas. If i want to set up a formula to where if i type "complete" in the cell it will move the entire row over to the next sheet.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Copy Cells Where Another Cell On Same Row Meets Condition
I have 2 sheets, sheet1 and sheet2. On sheet1 I have a cell (w,3) which displays either "OK" or "Needed". It will be like this for every row on the sheet. On sheet2 I have more information. I have been trying to create a macro or VB function that everytime the workbook is opened will scan the entire column W for any that say "needed" and if/when one is found copy the data in the cell in column A of the same row from sheet1 to column A of the first blank row in sheet2. So say cell w,6 displays "needed", I then want to have cell a,6 copied from sheet1 to sheet2 into the first column A that is blank. I don't really care if when w,6 changes to "OK" it removes the entry on sheet2 or not, in fact if it does that would be great. Here is the code that seems to be not so complicated and has copied information from one sheet to another, can't get it to work consistantly. I copied it off a similar post on this site and am not sure what the last value = "1" is doing but I doubt its helping my cause. The cell I need to trigger the action is in the 23 column, the cell I need copied is always in the first and the cell I need it to be copied into on the 2nd sheet is always in the first column as well. I'm pretty good with regular logical statements but I feel that a lack of knowledge about all the available commands is really whats hurting me on this....
View Replies!
View Related
Delete Row If Date Meets Condition
I have a spreadsheet with about 225 sheets or so containing data. The date is in column J. I am needing to delete all the Rows that have a date greater than or equal to 06/06/08 and loop through all of the sheets. I recorded a macro to find the date 6/6/2008, then highlight the row, then select the next 450 rows and delete them and it worked ok, but I don't know how to get it to loop through the sheets to the end.
View Replies!
View Related
Color Cell If First Character Meets Condition
I currently have a report that I have code on that will change the color of a cell based on whats in the cell.( Code posted below) However I also need to look at $J and if the first letter of the value in that column is a "z" then I would like to change that whole row to icolor 13 (purple). For example J14 might be "Z-Thomas". if it does I would like that row 14 to change to purple. Also I would prefer that the solution be in VB, so that we do not have to do something in VB with the fix below AND do something in Conditional Formatting. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("$A:$E")) Is Nothing Then Select Case Target Case "y", "Y" icolor = 4 Case "n", "N" icolor = 3 Case "?" icolor = 6
View Replies!
View Related
Sum Cells Until Adjacent Cell Meets Date Condition
Two Sheets: 1) Top Sheet 2) IRA - Cells A4:A10 of sheet "Top Sheet" contain dates. i want B4:B10 to show the IRA value as of the date in column A: ____A__________|_B______________________ 4 | 09/22/2005 | $ Total as of date in A4 5 | 10/06/2005 | $Total as of date in A5 6 | 10/20/2005 | $Total as of date in A6 7 | 11/03/2005 | $Total as of date in A7 8 | 11/17/2005 | $Total as of date in A8 9 | 12/01/2005 | $Total as of date in A9 10| 12/15/2005 | $Total as of date in A10 - Cells A6:A12 of sheet 'IRA' also contain dates but not in the same sequence (some date ranges had several transactions), B6:B12 of sheet 'IRA' contains the transaction values during the date range: ____A__________|_B______________________ 6 | 09/22/2005 | $100 7 | _ _ _ _ _ _| $200.................................
View Replies!
View Related
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 Replies!
View Related
Delete Nth Row In All Worksheets If Cell Meets Criteria
I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table". Sub DeleteFirstRowInWorksheet() Dim SheetName As Worksheet Dim i As Integer For Each SheetName In Sheets If Range("A1") = "table" Then Rows("1:1").Select Selection.Delete Shift:=xlUp End If Next SheetName End Sub It delete only in an active sheet. What's wrong?
View Replies!
View Related
Format All Rows Where 1 Column Meets Condition
I'm trying to Multi-Select rows in order to format all rows, in "One Step" if the cell in col. "A" = 1. I managed to write the hereunder code but I'm still frastrating because I don't like the first RNG setting command. How can I avoid declaring RNG as row(1) and still be able to run the code. I assume I need some other Range set for RNG which, unfortunately, I could'nt find and the command I used includes row1 in RNG although cell A1 = 5. Sub Conditional_Multi_Rows_Select() Set RNG = Rows(1) ' this is my problem LR = [A65536].End(xlUp).Row For R = 1 To LR If Cells(R, 1) = 1 Then Cells(R, 1).EntireRow.Select Set RNG = Union(RNG, Selection) End If Next RNG.Select Selection.Font.Bold = True Selection.Font.ColorIndex = 3 End Sub I had in mind to use: Set RNG = Rows(65536) but this will not make it a "neat" vba code, either.
View Replies!
View Related
Insert Cell On Condition And Move Right
I have made really huge downloads out of our SAP system (needed over 20 separate Excel files ;-). I need to analayze the data, but unfortunately the date is not 100% consistent as some cells are emtpy. Is it possible to have Excel look down a column, and have it insert a blank cell in another column on the same row where it found the empty cell? (and move the cells right a a result?) I have attached an example of what Excel should do as it is hard to explain (as you can tell ;-). I am likely to spend the next days figuring out how I can best combine the data and analyze it!
View Replies!
View Related
Copy & Append To Report, Rows From Multiple Sheets If Any Cell Meets Format Color
I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet. On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.
View Replies!
View Related
Macro To Move A Range Of Data Over 1 Cell, If A Certain Condition Exists
I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example. 14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178
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
Automatically Copy Row Meeting Condition On Double Click
Split from Copy Rows, Meeting Criteria, From Multiple Worksheets & Append To Summary Sheet This has been wrecking my head for a week. For the debtors tab it is all of the rows which have the value "Unpaid" for each of the 12 months that need to be copied into the tab. For the invoice I thought there could be a button at the end of each row and when you clicked it, it put the info into the invoice template and then the person could just print it off and resend it. And then do the same for the rest of the invoices.
View Replies!
View Related
Find The Last Row Which Meets Criteria??
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate). Column J contains scores (if present). I need column N to list the last row number where each column C name scored points (not just when heshe was last present). I think I need macros which I can fill down both columns (??).
View Replies!
View Related
Find Last Row And Score Which Meets Criteria?.
s/s has 325501 rows. Column C contains names of people (whether present or not -I enclose small attachment to illustrate). Column J contains scores (when present). I need column K to list last row where column C name scored and column L to list last score. I think I need macros which I can fill down both columns (??).
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
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
Transfer A Row If A Cell Is Matching A Condition
I have 2 sheets in an excel folder and in fact I would like to create a macro to tranfer a row of the chart present in Sheet 1 if a cell is matching a condition so that the macro identifies each row of the chart in the Sheet 1 and transfer the row in the other sheet if the value in the column A (Chart Sheet 1) is equal to 1 : sheets("Global Sheet").Select Range("A1").Select If activecell = 1 Then Transfer A1:J1 in the other excel sheet ( anywhere)
View Replies!
View Related
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
Match Condition And Copy To The Next Empty Cell
I am trying to do the following....I have data in three column in the "historydata" sheet, A, B, C. A lists the dates, B lists categories, and C lists some data. The data in column C is defined by the date and category in column A and B. What I want to do is...I want to copy data in C to the next empty cell in another spreadsheet if it meets the date and category condition. (for instance, if (y,A)=9/17/2007 and (y, B) = bond A, then I want to copy (y,c) to the next empty cell in sheet "marginreq" based on the first column. Here is my code...but it doesn't work. Sub OJOM() Dim A As Integer A = 2 Do While Worksheets("Historydata").Cells(A, 1) <> "" If Worksheets("Historydata").Cells(A, 2).Value = Worksheets("MarginReq").Range("B5") And Worksheets("Historydata").Cells(A, 3).Value = Worksheets("MarginReq").Range("B7") Then Application. ScreenUpdating = False Worksheets("Historydata").Cells(A, 3). CurrentRegion.copy Sheets("MarginReq").cells(rows,count,1).End(xlUp).Offset(1,0) Sheets("Historydata").Select Application.CutCopyMode = False Application.ScreenUpdating = True End If Loop End Sub
View Replies!
View Related
Color Entire Row When Cell Condition Met
Using conditional formatting is it possible shade an entire row when a cell is = to 0. I am currently creating a spreadsheet for a small amount of inventory. I am manually editing the amount of product available. What I want to happen is when I replace them number with the text ‘Sold Out’ I want then entire row to automatically grey that row out. Is it possible to make this happen using conditional formatting?
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
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
Color Table Rows Where Specific Cell In Row Matches Condition
I have a selection on a worksheet that has 4 consistent columns (A, B, C, and D) each with a variable number of rows all containing string type data. With this data I want to use Conditional Formatting so that when I have a string value of X in column D for any row, that whole row with the X in column D has it's color change to say grey, this should happen regardless of what strings are in Columns A, B, or C. Below is the way I've been trying to do this thus far and failing, when I run this code below I can only get it to grey out the cell with the X not the whole row. Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="=""x""" Selection.FormatConditions(1) . Interior.ColorIndex = 16
View Replies!
View Related
Coying A Cell Value If It Meets A Criteria
i am trying to get my excel formula to say if 2 date ranges in my excel workbook are the same (in seperate worksheets) to put a value from a certain cell into another cell in another worksheet. This is all in the same excel application... The first step i have taken are: 1) if the 2 date ranges are the same to say 'true' if not 'false' I am trying to get a forumula to say "if it is true to put a value from a different cell into another cell (both in different worksheets) but the same excel application...? but if its false to move on to the next date
View Replies!
View Related
Color Cells Where Cell Below Meets Date Criteria
I´m trying to do a macro that changes the color of all fonts in a row if the cell in the column "R" has the date lower than a specific one, something like this: Dim Data2 As Date Data2 = Sheets("Sheet1").Range("today") Range("R12").Select Do While ActiveCell <> "" If ActiveCell < Data2 Then ActiveCell.EntireRow.Select Selection.Font.ColorIndex = 3 Else ActiveCell.Offset(1, 0).Activate End If Loop But it just don´t work. The macro does that in the first row, than it stops. Do you have another code for this, or another way to do?
View Replies!
View Related
Auto Change Row/font Colour And Move Row
Attached example sheet. Basic table of data, with column F being a validation list, is it possible that when choosing COMPLETE from the list, the row and font change colour, and then move to the top of the list? I'm not the only simpleton using the sheet so I need it to be as simple as possible. I know it doesnt seem like a big thing but the sheet we are working from is huge and I dont want people cutting and pasting away, I just dont trust them with my spreadsheets.
View Replies!
View Related
Cell Content > 255 Then Move "excess" To Row Below
I'm looking to combine a number of worksheets into a summary sheet .. but on some the narrative is more than the 255 characters. How can I , where this is the case, insert a row below.. say input "Continued" in column A and then copy the contents of the cell which is more than 255 to the cell immediately below it.. and maybe do this again where users input more than 510 characters. Alternatively, is there a way of getting around the copy and paste values.. where it only inputs 255 ?
View Replies!
View Related
Copy Row To Sheet With Same Name As Value Of Cell In Row
I have a workbook that has about 300 different sheets- each the name of a different chapter of a fraternity. I get new lists of members daily and have to manually sort them into the 300 different sheets. Is there any way I can automate this? For example, if column "E" contained the chapter name- in this case cell E38 contained "Oklahoma Kappa" , the macro would automatically copy row 38 to the first empty row in sheet "Oklahoma Kappa".
View Replies!
View Related
Insert Row And Copy Cell From New Row
Attached is the sample workbook. I have a workbook with 2 sheets. Sheet1 contains all question and answer question. When the user select "Comment" as an answer, it will trigger to insert a new row on sheet 2. My question: is there any way I can copy from the comment fill in column c on new row to Sheet1 " Comment column"?
View Replies!
View Related
IF And OR Statement (populate A Cell Based On Which Number Meets The Criteria I Define)
I am trying to populate a cell based on which number meets the criteria I define. This is based on sales revenue, so if the revenue is less than $6.5M, I want to use a certain value. If the value is equal to $6.5M but less than $8M I want to use another value and finally if the revenue is greater than $8M i want to use another value. Here's my formula, but it returns $0. =IF(B2<6500000,Bonuses!J45)*OR((B2>6500000)*AND(B2<8000000),Bonuses!F45)*OR(B2>=8000000,Bonuses!B45)
View Replies!
View Related
Sum Row Based On Condition Refering To A Different Row
I have a problem with bringing up the data I need to cells in one row. In row 1 I have dates: 23-Nov 24-Nov 25-Nov 26-Nov etc. In row 2 Quantities: 2000 1500 250 750 etc. In row 3 I have dates again (I need to start working towards these qty's): 20-Nov 22-Nov 25-Nov 25/Nov. I would like in column A of 4 row to sum all the qty's (row 2) if the date in row 3 is the same or smaller than in row 1 column A. But in column B and forward I want cells to sum qty's if the date in row 3 is the same as the one in row 1. I have tried : =sumproduct((A3:D3<=A1),(A2:D2)) for cell A4 and: =sumproduct((A3:D3=B1),(A2:D2)) for cell B4 and consequently for others. It comes back as zero value even if I see matching dates/qty's. Does anyone know what shall I change in my formula to make it right?
View Replies!
View Related
|
TRACKER
