Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Automatically Move A Number To A Different Cell
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 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
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 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
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
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
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 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
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
Two Column Sort And Move
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 Replies!   View Related
Move To Next Column After Sort
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 Replies!   View Related
Sort, Average & Move
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 Replies!   View Related
Auto Sort- Row Refernce Based On Cell
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 Replies!   View Related
Automatically Update A Protected Cell Based On Contents Of An Unprotected Cell
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 Replies!   View Related
Create Macro To Sort And Move Data
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 Replies!   View Related
Sort And Move Code, Create Equipment Lists`
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 macro’s 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 Replies!   View Related
AutoFilter Automatically Based On Cell Value
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 Replies!   View Related
Automatically Send Email Based On Cell Value
I have created a detention tracking sheet for my school. I need an email to be automatically sent to the teacher when a student does not show up for detention.

Here is what I have currently coded, but the macro is not running, and the email is not being sent. I do not understand why.

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....

View Replies!   View Related
Automatically Color Rows Based On Cell Value
I currently use spreadsheets to report on backup processes.

I am trying to speed up this process and one thing that woudl help me is a function that will automatically fill the row with a colour depending on the choice in the dropdown box, so if you choose 'successful' from the dropdown list the row colour would fill to green and if you select 'failed', the row would change to red and a choice of 'completed/errors' would result in orange.

This process is currently done manually and often results in errors like a succesful result with a red filling.

Is there anyway to automate this process? i was trying to whip up a macro, but have been unsuccessful so far.

View Replies!   View Related
Automatically Populate Cell Based On Worksheet Name
I have a macro that copies data from 6 different worksheets into one summary worksheet - based on certain criteria - I need a way to note on each row of the summary sheet which worksheet it was copied from. Each worksheet is specific to a salesperson, so ideally I would like that salespersons last name to populate in column on the summary page for each line of theirs that is copied over....here is my current macro I use to copy the data: (this scrpit is repeated for each sales persons sheet - "Blankenship", "Dew", etc...)

View Replies!   View Related
Name Sheet Automatically Based On Changing Cell
In my sheet called summary I have the names of the rest of the tabs in the book in cells B5 to B34. We want to be able to change the names of the tabs by changing their respective cell on the summary tab. So “sheet1” corresponds to B5, “sheet2” to B6, “sheet3” to B7, etc.. So if I change the name in B5 to say “APPLE” I want the tab for sheet1 to change to APPLE. When I change B10 from “sheet6” to “Lemon” I want sheet6 to be titled “Lemon”.

View Replies!   View Related
Automatically Name All Sheets Based On Cell On Sheet
I have recently used the following code to name a sheet from a cell within that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("D1") Then ActiveSheet.Name = Target
End Sub

What do I need to add to this bit of code to allow me to name the sheet from another sheet's cell i.e Sheet1 gets named from say Sheet2 Cell A2

View Replies!   View Related
Automatically Move Data To Another Sheet
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 Replies!   View Related
Import And Move Cells Automatically
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 Replies!   View Related
Automatically Move Rows To Another Sheet
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 Replies!   View Related
Automatically Fill Cell Value Based On Another Cell
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?

View Replies!   View Related
Automatically Enter Cell Value Based On The Values Of Two Other Cells
I am working on blood pressures. I want cell C1 to place either, "Prehypertension","Stage I Hypertension" or, "Stage II Hypertension" depending on the values of cells A1 or B1 ....

View Replies!   View Related
Automatically Clear Range Based On Cell Criteria
I have the following Worksheet Change Event in my worksheet. The contents of range rng are not cleared.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range

rng = Range("F" & Target.Row & ":M" & Target.Row)

If Not Intersect(Target, Range("N5:N1000")) Is Nothing Then

If Target.Cells.Count = 1 Then

If Target.Value = "Yes" Then

Application.EnableEvents = False

rng.ClearContents

Application.EnableEvents = True

End If

End If

End If
End Sub

View Replies!   View Related
Automatically Calculate 2 Cells Based On Cell Changed
I have some rather complex formulas that when a value is placed in one of any three cells it solves for the other two, taking values from other places on a worksheet (including the cell itself).

Scenario: Cell F10, G10, and H10 all have formulas in them. If a value is placed in F10 it solves for G10 and H10. If a value is place in G10 it solves for F10 and H10. If a value is placed in H10, it solves for F10 and G10.

I really do not think the actual formulas matters at this time...

Has anyone done this before, using VBA code to place the formulas in cells F10 G10 and H10? Look for a working example of this if possible.


View Replies!   View Related
Automatically Hide / Show Rows Based On Cell Value
I wrote a small code to hide some columns if a certain cell is equal to a certain string.

The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.

Sub Auto_Open()
Range("A1").OnEntry = "Action"
End Sub

Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method

View Replies!   View Related
Automatically Filter Worksheet Based On Changing Cell Value
Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.AutoFilterMode = False
Range("d6:g6"). AutoFilter
Range("d6:g6").AutoFilter Field:=2, Criteria1:=Range("e1"), Operator:=xlAnd, _
Criteria2:=Range("e2")

I've attached the file also.

What i intend to do is that : (1) if i enter a value in E3, the filter should only apply using E3 value (currently its applying E3 value but if E4 is kept blank, it takes that as = " " ) . Unfortunately, i need to have the and condition, so i have to find a way in spite of this condition. Any way out ??

(2) If i enter values in A) E3 & F3 B) E3, F3, G3...then it ahould make multiple filtering possible. But when i try to apply such a condition, the same problem as in point (1) occurs, it takes the and empty criteria range as = " "

View Replies!   View Related
Tally Cell Automatically Based On CheckBox State
In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
Select Case Target
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Logbook.Show
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")
Logbook.Show.................

View Replies!   View Related
Automatically Fill Cell Down X Rows Based On 2 Other Cells
Suppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)

View Replies!   View Related
Automatically Format Range Based On Specific Cell
I am looking for VB code that will format a range of cells from a number format to a percentage format based on a reference cell. The reference cell is a drop down created from a list validation.

For example: If the reference cell (cell A1) =1 then cell range B2:C4 would be a number format. If A1 = 2 then cell range B2:C4 would be a percentage format.

I have searched for hours trying to find a relevant thread; I know they are out there, maybe I just wasn't typing in the correct search words.

View Replies!   View Related
Automatically Check Option Button Based On Cell Value
Is there a way to set up a "watch" on a cell so that if you type in a different number on a cell, OptionButton1_Click() gets activated? Example:

A9 = 12.0104
OptionButton2 is active.

In Cell A9 you type "25.0508". OPtionButton2 becomes inactive and OptionButton1 becomes active. I already have the buttons linked, i just do not know how to make the button get triggered if A9 changes.

View Replies!   View Related
Automatically Hide/Unhide Columns Based On Cell Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub

but I have a lot of columns that I need to perform as above and I have put the code together as below

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................

Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.

View Replies!   View Related
Automatically Sort By Date
Here is what I want to accomplish:

I have a spreadsheet with data in cells A36 to G52. In The D column (D36 to D52) I have a date in there. I would like to have this column sort in ascending order automatically, but also have it sort the other data within the same row with it.

I have tried a couple of VBScripts that I have found in other posts that are similar, but I just get errors when trying it so I assume that I am not putting something in correctly.

Can anyone help me write this functionality? I have other data in a similar setup on the page that I also want to sort automatically but the script for this first part should be just a copy and paste, and change a couple of values....at least I am assuming so.

View Replies!   View Related
Sort A Column Automatically
Is there a way to automatically sort a column in ascending order while entering numbers so that the column is sorted when last number is entered.

View Replies!   View Related
Macro To Sort Automatically
I am now trying to write a macro which is a little over my head and was hoping some one may have some idea how to do it or have some code that is similiar and I can try to customise it myself. So here goes.

Basically all that happens is I paste data into a work book that always has two columns. The first column is a list of names and the second column is a list of numbers. I need to sort that information automaitcally every time it is pasted into the work sheet as follows.

1) Once any information is pasted to sheet 1 run macro automatically with no user interaction

2) All numbers must be rounded up or down first before sorting. If it is say 2.4 then it would become 2 and it it was 2.6 it would become 3.

3) Then move the numbers only from a specified column from sheet1 to sheet 2 and split them up into seperate columns based on the following results.

a) 0 - 3 gets moved to column B
b) 4 -7 get moved to column C
c) 8 - 10 gets moved to column d

4) Delete both columns from sheet1 after this is run so the user can re paste new information

5) If a user does this again then do the same but move into a new set of columns e f g and so on until the work book ends.

View Replies!   View Related
Automatically Sort Table
I am currently in the process of creating a league table using Excel, and was wondering how I could get the teams to automatically arrange themselves in order of total points?, without using macros, is any formula capable of this?

View Replies!   View Related
Automatically Sort New Data
I have (what seems to me) a somewhat complicated request. I’m trying to do a custom workbook without using VB (if possible). I’m trying to design a file list that can be updated easily by anyone. The new row of information needs to be entered (by a data form maybe?) and then automatically alphabetized by ‘ case name”. See below. I don’t know Excel formulas that well. This seems fairly easy, but I am at a loss how to do it.

Case NameCase #Open Date Closed DateRep

1Apple, Aaron2006-45 12/8/06 Tom
2Apple, David2004-02 01/14/04 Bill
3Bennett, Tim2006-0411/28/06 Bob
4Carter, Jimmy2005-23 04/22/05 Chris
5Carter, Tim2000-11 08/02/00 Jim
6Ford, Gerald 2007-206/06/05 Jill
7Gold, Bill 2006-7803/12/04 Pat
8Hill, Jim 2006-7703/18/04 Amanda
9Janes, William2005-6803/14/05 Dave

I would like to enter a new record (“case name -e.g. Jones, Smith, etc.) , with all the other corresponding info (Case #, Open Date, Rep #) and have excel automatically insert it ALPHABETICALLY by case name into the sheet and also, if possible, renumber. I know I can add the new record it to the last row and the Sort by case name, but I wanted to have others do it, and when they try to enter and sort the spreadsheet it always gets messed-up.

View Replies!   View Related
Move A Number From A Group Automatically And Post In A Different Colume
I would like to move a number from a group, automatically and post in a different colume. Like remove the 4 from 439 to a different colume.


View Replies!   View Related
Information To Enter The Tables And Then Automatically Move Into The Next Available Cells Below
I am trying to use visual basic editor in excel. I have all ready set up my user form where information can be entered, but I ave having trouble getting the information that is entered in the user form into the correct cells in excel.

I am wanting my information to enter the tables and then automatically move into the next available cells below.

Private Sub cmdadd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("details of cars in stock")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.txtreg.Value) = "" Then
Me.txtreg.SetFocus
MsgBox "please enter a registraion number"
Exit Sub
End If
ws.Cells(iRow, 1).Value = Me.txtreg.Value
Me.txtreg.Value = ""

View Replies!   View Related
Task To Automatically Move To Another Worksheet For The Relevant Month
I have created a spreadsheet for internal use in my workplace that logs task by members of staff, What I am trying to achieve is when someone selects from a drop down box that a task has been complete I want the task to automatically move to another worksheet for the relevant month, as I will have worksheets for every month logging all the past tasks that have been assigned. I believe an IF statement should do the trick, but I am having no luck so far.

View Replies!   View Related
Automatically Call Macro Based On Changed Cell Address & Text
I have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working.


Private Sub WorkSheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$16"
Select Case Target
Case "Custom Color 1": Call CustomColorInput1
Case "Custom Color 2": Call CustomColorInput1
Case "Custom Color 3": Call CustomColorInput1
Case "Custom Color 4": Call CustomColorInput1
Case Else:
End Select
Case "$A$17"
Select Case Target
Case "Custom Color 1": Call CustomColorInput2
Case "Custom Color 2": Call CustomColorInput2
Case "Custom Color 3": Call CustomColorInput2
Case "Custom Color 4": Call CustomColorInput2
Case Else:
End Select
Case "$A$18"

CustomColorInput 1-6 are the same except they input data into different cells F16-F21.

When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code.

Function PullAfterLast(rCell As Range, strLast As String)
PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256)
End Function

When I close VBA, the data that I typed into the data input box is in the correct cell.

So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on?

More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?

View Replies!   View Related
Automatically Sort In Descending Order
I'm using a table with columns from A to F and an unlimited number of rows. Note that there is a title at the top of each column.
-I'd like my datas in column F to be automatically sorted in descending order from top to bottom when I hit the enter key or when the number is entered in last column (column F).
-the the entire row of data needs to be sorted according to the sorting criteria of column F ( datas entered need to stay on the same row)
-the macro should validate that the number value entered in column F is the same as the number value entered in column A. (to make sure that there was no typing mistake)
-Result in column D must be the difference between the corresponding value in column B and C (B-C=D)
*The next couple of constraints are not absolutely required, i'd like to have them only if it's possible to add constraints with filling color:
-Whenever there is no value entered in column C on a specific row I'd like the entire row to be colored in yellow (fill color)
-IF a number is entered in column C, and that the result of B-C in column D is not equal to zero, I'd like this specific row to be filled in Red.
-when all the values are entered in a row from column A to F and that the result of B minus C is equal to Zero in column D, i'd like this specific row to be filled in a green color.
***in other words, the datas in each rows from column A to F will be entered on 2 occasions; on the first time, only the datas in column A,B and F will be entered (so basically after entering this set of values the row should be filled in yellow. the second time, the remaining values will be added to the rows, so at this time A,B,C,D,E and F will all be entered. when this is done there will be two options
1-the value in column D is equal to Zero then the entire row should be filled in green or
2-the value in column D is'nt equal to zero, then this specific row should be filled in red.

Keep in mind that the datas of a row should be automatically sorted only when the value in column F is entered or when the enter key is hitted to add a new row.

View Replies!   View Related
Sort Data. And Automatically Fills
Scenario: You have two columns in a spreadsheet. Column A is a text input and column B is a tracking number for each row. Using the ISTEXT statement, I had it configured so that when a cell in column B recognized a TRUE statement in an adjoining cell in column A it would then assign a number. B1 is assigned a number when A1 has text.

What I would like to happen is build the spreadsheet so that after the user enters text in column A, column B automatically fills in so that the user doesn't have to manually enter it. I've figured out how to do this if the data is never sorted, however, if you sort the data then the tracking numbers don't follow and are really a mess.

Is there a way to auto fill column B and at the same time, make it so that the tracking number that is originally assigned to the row follows with A when sorted?
I've tried using =MAX but run into circular reference issues.

View Replies!   View Related
Automatically Sort Information Into Different Sheets
I have a sales sheet that records every sale as it comes in. I want it to copy the relevant information (only that sales reps sales) into each sales reps individual sheet automatically so that I can send each rep his own sheet. Can this be done? SEE ATTACHMENT

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved