Automatically Move & Sort Row Based On Cell Value

Sep 20, 2008

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: ...

ADVERTISEMENT

Move And Sort With One Column But Insert Extra Columns As Needed For Proper Sort?

Jan 13, 2014

Using DataEntry sheet for data.
Trying to rearrange the data to DataFormatedProperly sheet.
So far all I can accomplish is DataFormatedWrong sheet.

Edit: Not sure what happened but file was NOT understandable before. It should be correct now.

View 2 Replies View Related

Automatically Move A Number To A Different Cell

Oct 5, 2009

i've got a worksheet with a column of unique numbers that i sort from smallest to largest; colA. these numbers are asset #'s of all items that i should have in inventory and all are barcoded.

using a scanner i scan the barcode # into colB however these items are on the shelf in random order.

as it stands i place my cursor in the first empty cell of colB (B2) the scanned # is entered into B2 and the cursor moves down to B3.

What i'd like is to have the randomly scanned number automatically move to the cell in colB that is beside the corresponding # in colA regardless of where the cursor is prior to the scan.

e.g.: 16 is in A16, cursor is in B2 waiting for first scan, first randomly scanned # is 16; so 16 is automatically entered into B16 instead of B2.

View 9 Replies View Related

Automatically Sort Data And Ranking Based On Sorted Data?

May 22, 2012

In the attached spreadsheet I track the performance of my team. I enter the AHT for my team in the work sheet named "AHT Summary".I keep updating this data every month. automatically sort the updated data and rank the agents based on their average AHT. The person with the lowest AHT should be ranked 1. Column is highlighed in green for your reference. Based on this ranking the work sheet named "Ranking" should be updated automatically. Since I am taking a weighted average for all agents, the one who gets the lowest AHT should be ranked 1st . In the ranking work sheet the agent with ranking 1 should be given 100, the second highest ranked person should get 98,third 96,fourth 94 etc.

View 6 Replies View Related

Move Row Into New Sheet Based On Cell Value?

Dec 5, 2012

I need write a VBA code that will move an entire row into a new sheet if the value of the last cell says "YES" If the value is blank then Id like it to stay in the current sheet. I would also like it if the cell wont allow any other value other than Yes to be typed.

I found this code here:

Copy, Move and Delete Row, based on Cell Value

THE VERY FIRST CODE REPLIED IN THAT THREAD.

I just cannot seem to get it to work. Simply copying and pasting that into the module doesn't work. I am not very good at VBA and just started taking a course on VBA. Id like to jump into this problem asap as I need it for work.

Basically:

Columns A:E will have values. In Column E I would like to type in Yes if completed. I would like the Macro to run through all of Column E and if the cell value is "YES" then I want it to cut the entire row and paste into a new sheet. In the new sheet I would like it to be pasted into the next available row.

Also can I create this with a ActiveX control button? I would like to have a button that I can click at the end of my work which will run the Macro in sheet 1.

View 6 Replies View Related

Move To Next Column After Sort

Mar 24, 2007

Need the formula that I will need in my macro to move to the next column (up one letter) for the next time I run the macro witout me having to manually change column the letter?

View 15 Replies View Related

Two Column Sort And Move

Mar 13, 2009

I have a look up table on sheet 1, that looks like this table to the right. On sheet 2, I need it to find the table contents in the column CC, then look at a column AA and find all that match it and move all that match this cell in column AA and copy the row it to sheet 3.

Its 300 lines of data, a couple of time of week.

It is a sort of data with a twist.

AABBCCDDEETreeDogCathousecardavemanboyjobbaseTableboatsamlaurasamjimboydaveweedwalkwalkruncatfunlaughboydivefloatTreeDogCathousecardavemanboyjobbasedaveweedwalkwalkrunfunlaughboydivefloat

View 9 Replies View Related

Sort, Average & Move

Jul 16, 2007

I am a buyer of hundreds of different types of items. I get thousands of different price quotes each day for hundreds of different items, 3 of which are balls, gloves and bats. I don't have the time to go line by line through thousands of quotes for hundreds of items each day to find the best deals. I want Excel to point the best deals out to me automatically, perhaps by making a cell.

Basically I need excel to sort my data by item type.

Then, calculate the average price for each item.

Then, point out any price quotes that are 10% below the average price, perhaps by making that quote in a red box. Attached is a spreadsheet with an example.

View 6 Replies View Related

Macro To Move Cell Down A Row Based On Criteria

Feb 20, 2009

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 3 Replies View Related

Copy, Move And Delete Row, Based On Cell Value

Jun 21, 2009

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 9 Replies View Related

Move Rows To Another Worksheet Based On A Cell Value In That Row

Jul 16, 2005

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 8 Replies View Related

Move Data Based On Cell Values

Jun 25, 2007

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 3 Replies View Related

Move Rows To Another Worksheet Based On A Cell Value In That Row ...

Jan 15, 2008

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 7 Replies View Related

Shorter Macro To Move All Lines Based On Cell Value

Jun 9, 2014

Basically I want to copy and paste all data in a row if the value in a cell is equal to something

I am currently using the below formula

The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).

But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.

So I am looking for a way i can add multiple values each going to separate sheets.

View 14 Replies View Related

VBA - Move Entire Rows From One Sheet To Another Based On Cell Value?

Jun 3, 2012

I have two sheets:

1. Not yet printed
2. Printed

My data is in sheet 1 (Not yet printed). I would like to move automatically entiry rows (sometimes more then one) to sheet 2 (Printed) based on one cell's value. Here is a screenshot:

For example when I enter into Sheet 2 '264450' then row 2 would have been moved to Sheet 2.

And also with multiple rows, if I enter '264461' then row 4 and row 6 would have been moved to sheet 2 as well. I wouldn't like to enter a value more than once.

View 1 Replies View Related

Move Entire Row Based On Date In Cell - Macro?

Oct 30, 2013

Ok so Im working on Christmas functions and this is how my data base looks

Date
Name
Address
Seats
Price
Total

1/12/2013
James Fran
1 Bold street
4
£20
£80

[Code] ......

What I need to do is move all the dates onto their own worksheet, so every row containing 1/12/2013 would move to the worksheet 1.12.13, the 6/12/2013 would move to the worksheet 6.12.13 and so on.

Currently I have to copy and paste each row manually, which takes time. Any macro or formula which would do this automatically every time new data is entered. The main database, like above, will contain around 200 entries.

View 2 Replies View Related

Module To Find & Move Based On Cell Adjacent's Set Value

Jul 27, 2007

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 10 Replies View Related

Move Files To Dynamic Folders Based On Cell Value

May 13, 2007

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 6 Replies View Related

Move Range Of Cells Based On Cell Criteria

Nov 16, 2007

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 7 Replies View Related

Move Rows To Worksheets Based On Cell Text In Row

Mar 16, 2008

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, Im not sure how to implement it into my situation.

View 6 Replies View Related

Move Rows In Table Based On Cell Values

May 1, 2008

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 6 Replies View Related

Create Macro To Sort And Move Data

Jun 5, 2009

I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.

I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.

The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.

View 9 Replies View Related

VBA - Macro To Move Textbox Entry To Cell Based On Date

Dec 4, 2011

I have a row of dates in row 2 (all sequential, from Nov through to June next year)

I have a row of number entries in row 3 (to correspond with the date in row 2 it was entered on).

I want to be able to add a new entry (a number) into a text box, click a macro button, which makes the text box input be moved into the cell underneath todays date (so every day the cell will move one column along)

I've managed to make myself a textbox, and a macro button, but I'm stuck with how to make it all work.

View 5 Replies View Related

Auto Sort- Row Refernce Based On Cell

Feb 18, 2009

I am typing a number into Cell B4 (ex. zza4582) this number is a serial number that will be in many differnt cells in column "A" on a different sheet "Template Paste". On each row with that serial number I need the information from "A-I" to be copied in the main sheet starting on row 10 and continuing on from row to row depending on how many times the serial number is found.

View 10 Replies View Related

Sort / Move Data In Accordance To Specific Column?

May 9, 2014

How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB

View 9 Replies View Related

Sort And Move Code, Create Equipment Lists`

Mar 5, 2007

I have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets.

On my 'final list' sheet I need the first 10 rows to be left out of the macros so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does.

I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.

View 4 Replies View Related

Excel 2010 :: How To Move Graph Based On Relative Cell Reference

Dec 8, 2011

did in [URL] but my issue is complicated by not knowing what cell I need to move the graph to.

I am using Excel 2010 and I am a realitive novice at writing VBA code.

In My spreadsheet, I have a list of properties and some related cost info. From week to week, the number of properties varies. Some days I will have 6 properties and others I could have 100. Because of this, I need to make all of my references relative to other cells. I am having trouble repositioning the graphs that I have created from the data to be 2 cells below the label I have created for the graph (which is a merged cell covering Columns B:I on a row 3 rows below the last property in the report.

So far, I have:

Dim r1 As Range, r2 As Range, GraphRange As Range
Cells(2, 1).Select
Selection.End(xlDown).Select

[Code]....

View 1 Replies View Related

Move Latest Updated Cell In Sheets In A Workbook Based On Time And Date

Mar 1, 2014

I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.

Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.

View 5 Replies View Related

Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell

Apr 30, 2009

I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.

For example:

Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.

If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.

View 5 Replies View Related

Automatically Move Cells Down After Entry?

Aug 1, 2014

Is there a way to automatically move cells down after I entered data in it? This way I do not have to scroll all the way to the bottom of the list to add new data.

View 5 Replies View Related

Automatically Move Data To New Sheet

Feb 12, 2014

Is it possible to create a cell in one sheet, where after data is entered, will move it to another sheet. Additionally, the data in the cell, after the transfer to another sheet is made would delete itself. Once the data is entered, sent to another sheet and deleted, next time data is entered into the same cell, it would be sent into a row below the cell that the first piece of data would be in. Therefore, after I enter data, [for example] press enter, then go back to input some more data, after a while there would be a long list in another sheet. The last feature of the problem (it would be something extra really) is that the function/macro is to be time dependent - during one month the data would stack up in one column, after the next one begins, the next column will automatically start all over.

The idea is to create a budget book - where on the main page I would only have to enter the value in a specific box, the value then would be automatically sorted into the right row and column in another sheet, after the month/week ends, data would start to stack up in a column devoted to a new month/week.

Of course the data could be entered manually in different sheets, but it proved to be too complex to be useful.

View 1 Replies View Related

Automatically Move Data To Another Sheet

Jan 23, 2008

I have Excel 2003. I need a formula that will automatically move data in a row to another sheet, in the same workbook, based on either a two digit department code or possibly a date. I enter information on a master sheet, (8 columns of data: Text/$/digits) and I need that information to separate out to the other sheets when that department's two digit number is entered on this sheet. There are a total of 6 sheets in this workbook, counting the master. The data I am entering will be about 40-50 rows down of information, 8 columns across. Will this take a macro? Loop?

View 9 Replies View Related

Import And Move Cells Automatically

Oct 23, 2008

Every day we receive a text report via email (only way it can be received), so we take this text report print it out and manually type it into an excell sheet.

I believe this is a waste of time. Is there any way I could take this info and automatically place in the the excell?? Until know I found a text to excell converter and was gonna create a macro to automatically convert this info into the excell report.

View 9 Replies View Related

Automatically Move Rows To Another Sheet

Feb 29, 2008

I have a spreadsheet that contains large amounts of data. I've looked through the forums and tried to adapt other VB code to work for me all to no avail.

I would like a macro to automatically cut a row from sheet1 and paste it on to a new line in sheet2 when data is input into column 'X'. (The data input in to column X is a date the specific case is resolved)

View 6 Replies View Related

AutoFilter Automatically Based On Cell Value?

May 12, 2008

I am looking for a way to have some VBA code running in the background of a worksheet.

I would like the autofilter criteria to be based on a cell reference which can change based on what this cell value is?

View 6 Replies View Related

AutoFilter Automatically Based On Cell Value

May 12, 2008

I am looking for a way to have some VBA code running in the background of a worksheet.

I would like the autofilter criteria to be based on a cell reference which can change based on what this cell value is?

View 3 Replies View Related

Automate Recorded Macro - Sort Range Of Cell Based On Two Values

Jan 23, 2013

I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.

My recorded macro is:

VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1

[Code] .....

View 4 Replies View Related

Formula To Move Column Automatically To Different Sheet?

Apr 23, 2014

I have a excel file Sheet 1 is active data Sheet 2 is complete data, How can I make the column move automatic from one sheet to another with out cut/pasting?

View 1 Replies View Related

Automatically Move Data From Working Worksheet?

Dec 27, 2013

I'm looking to automatically move data from my working worksheet, when a particular column states "Won" or "Lost". I'm hoping the entire row of data can be cut, and added to the appropriate sheet, determined by "Won" or "Lost".

View 5 Replies View Related

Automatically Enter Text In Cell Based On Another Cell?

Jul 29, 2013

I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number.

What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).

Table A
Cab Type
Cab No

Indigo
100

Indigo
758

Innova
1544

Indica
1675

View 1 Replies View Related

Automatically Enter Text In Cell Based On Another Cell

Jul 28, 2013

I am trying to enter a value in a cell which is dependent on a corresponding value in a table. I have a table A which has a cab type and a corresponding cab number. What I want to achieve is that when I enter the cab number in another table say in cell (D3), the cab type is automatically entered in cell (C3).

Table A
Cab Type
Cab No

[Code].....

View 7 Replies View Related

ADVERTISEMENT