Macro To Move Cell Down A Row Based On Criteria
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
Insert Row Macro Based On Cell Criteria
I'm trying to create a macro that takes data from one row and inserts it into a new row. Attached is a workbook with a before and after example of what I'm trying to do. Each row has a security transaction that includes principal cash and interest. The data needs to be formatted so that each transaction has one row for principal and one row for interest. Principal is identified by the tran code PAYDOWN in column C. Interest is identified by the tran code INT. However, the raw data generated includes both interest and principal under the tran code PAYDOWN. There could be 100 or 1000 transactions generated based on the day.
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
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
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
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Move Rows Based On A Criteria
Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2" I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B
View Replies!
View Related
Move Data Row When Criteria Met
I have a spreadsheet with when column K is Yes I need to move the data on that appropiate row to the corresponding worksheet in the same workbook. I have the button working but it moves the data in the row if No is in column K which is what I don't want. It also moves - removes the variables but I guess that is another question for another day.
View Replies!
View Related
Move Data Based On Criteria In Column
Here is the situation: There is a spreadsheet that I use that has multiple plans in one column for various members. You can see the plan for Robert James and Amanda James is plan 5. If all this information was in column A how would you move the plan type say column C for each member? For example Column A Customer Name Robert James Amanda James Plan 5 Nikki Martinez James Gross Plan 6
View Replies!
View Related
Highlight & Move Cells Based On Criteria
I have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules. The rules are as follows: 1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date. 2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract). I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................
View Replies!
View Related
Macro Code: Find Cell Or Row Multiple Criteria
I can't seem to find the answer to this. In VBA, how do you Find a cell or row based on multiple criteria? All I need is to find it's location. If it helps, here's an example: Sheet 1 contains source data: Date Name Time Complete Sheet 2 contains an interactive worksheet using this data. On Sheet 1 there's an entry: 9/12/2006 George 03:30 PM Sheet 2 displays those records with adjacent cells for entering data. When data is entered, I need the system to go back to Sheet 1, find the entry that matches this one, and put an "x" in the Complete column.
View Replies!
View Related
VB Macro For Variable Cell Merge Into HTML Code Based On Criteria
Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code? The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number). I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.
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
Macro To Move Row To Another Worksheet
I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.
View Replies!
View Related
How To Hide/undide A Row, Based On A Cell Value (Macro)
I have the data as follows: columns B,c,d,e,f,g,h contains data and column A is empty. What i want is 1. Initially columns c,d,e,f,g,h should be hidden. Only a & b should be visible 2. if i enter a 0 (zero) in A1, then, columns B,c,d,e,f,g,h should remain hidden, for row-1. 3. if i enter a 1 (one) in A1, then, columns B,c,d,e,f,g,h should be visible, for row-1.
View Replies!
View Related
Identify Row Number Based On Value In A Cell And Use That Row Number In A Macro
I have Sheet with 40 employees who each proposes their work schedule, so I have to give each Employee access to the same sheet and want highlight and unlock only those cells that specific employee can use. Each employee has to login from a drop-down (sourced from Sheet.Employee Master), so their unique Employee Number is in "A13" of Sheet.LOGIN Can I identify the ROW number and then use that ROW number in a macro to highlight and unlock specific Range of Cells in Sheet.PROPOSED SCHEDULE? ---where "Sheet.LOGIN("A13") = (the value in the cell Col A:"row" of Sheet.PROPOSED SCHEDULE) I have attached a scaled down version of the Workbook. Following code is scaled down-- this is for Employee 02 who appears on ROW 16 of the sheet. (macro is same for each employee, just uses a different row)
View Replies!
View Related
Cut & Paste Macro: Move Address From Column To Row
I have an excel worksheet with contact info in columns rather than rows. For example, C1R1 is company name, C1R2 is address, C1R3 is City/St/ Zip, C1R5 is phone. I recorded a macro to cut and paste address to C2R2, C/S/Zip to C3R1 and phone to C4R1. That works but I cannot repeat it as I move down the list. Just re-runs macros over same cells all the time. How do I get it to start and run wherever I want it too? Also, how would I get it to delete the now vacant rows?
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
VBA Macro To Move Data To Summary Sheet Based On ID's
I have had several intro programming classes, but not with visual basic. I've noted the problem lines with comments below. The problems are probably very easy syntax error. Sub list() Dim counter As Integer Dim counter2 As Integer Dim id As Double Dim id2 As Double Dim palletnum As Integer ActiveSheet. Name = "Pallet Detail" Sheets.Add ActiveSheet.Name = "Pallet Summary" Sheets("Pallet Detail").Select
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
Repeat Row Based On Text Criteria
to run a macro or any other methods to repeat the same head rows in my database as followings: Let's say the main head row is in row 1. Item names such as Item A; Item B; Item C ....in column A What I want is to insert the same head row after the list of each item. Item name (head row) Book A Book A Book A Item name (head row to be repeated) Book B Book B Item name (head row to be repeated) Book C Book C
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 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
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
Combining Row Results Based On Specific Criteria
If I have the following table: Name ProductSize QuantityPrice per unit AX35510 AX451015 AX551210 BX601010 AX35510 I now want a resulting table that will combine all rows where name, produc, size and price are identical and have one row with the same data apart from the quantity that will have been added - so for the table above the resulting table will be: Name ProductSize QuantityPrice per unit AX351010 AX451015 AX551210 BX601010
View Replies!
View Related
Copy Row Data To Another Worksheet Based On Criteria
Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?
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 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
Finding Data Based On Row & Column Criteria
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX Result data(criteria base on Month & type) JanApril BananaXXXX Apple Orange
View Replies!
View Related
Move Cell Locatin In A Macro
Below is my the macro that I recorded. I want to copy and paste the same information to a new location everytime. The macro tells it to go to cell IV1, end left, then I want it to go right one cell regardles of the cell location. Here it tells it to got to ("K1"). It will do this everytime and I do not want it to go to K1, just move right one time and then paste the information.
View Replies!
View Related
Module To Find & Move Based On Cell Adjacent's Set Value
ABCDE FGHIJK If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be: ABCDEG F HIJK
View Replies!
View Related
Macro To Count Based On Criteria
I have ahseet with the following columns: Col 1 = Item Description Col2 = Price Col3 = Discount Rate I would like to gather a count on another sheet of all instances in col 1 where it is equal to 'Fridge' I know I can use the SUMIF function but I would like to do it in VBA code.
View Replies!
View Related
Macro To Move Data With Trigger Cell
I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice. Sheet1 = Prospects Sheet2 = New Sale Sheet3 = Upgrade Sale Sheet4 = Won Sheet5 = Lost Sheets 1, 2, and 3 use the same data layout for column A - K. Sheets 4 and 5 have the same A - K as above and also have columns L - R in common. The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost). Data rows on each sheet start at row 7. The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.
View Replies!
View Related
Macro To Move A Cell Right Every Time It Runs
I have a spreadsheet with sales figures for a company over a few months. I want to create a macro that will insert a new month after the last month. I have got it to work for the first month but after that it will just insert 2 black cells after. i have a named cell called Average_slales_per_month that is next to the last month all the time.
View Replies!
View Related
|
TRACKER
