I'm trying to do something quite simple but I can't seem to see the forest thru the trees.
All I want to do is when I deactivate a worksheet (sheet2) I want it to sort a predfined range. I keep getting a "Select Method of Range Class Failed" error message on the line of code with ******
The code is as follows: ...
I am using Excel 2003 and have used the Data > List > Create List... menu. I am after a worksheet deactivate macro that will sort the data within a data list first by column A and then by column B. I have found a macro that can be activated by a button when working on the worksheet:
Sub Sort_A_then_B()
With ActiveCell. CurrentRegion
.Sort Key1:=. Cells(1, 1), Order1:=xlAscending, _
Key2:=.Cells(1, 2), Order2:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End Sub
However it is important that the data within the worksheet is sorted by column A then B when I move to another worksheet. I would like the macro to be able to sort the data list by however many rows or columns there may be within the list. I assume this would include as follows: With ActiveCell.CurrentRegion
i want to automatic sort data very time i open the file or any change in column b.
i have data in column B from B1 to B100
how to collect data from Column B and place them in Column A star from A1 because Column B not Organized!
I mean B1 have data
B2 have null
B3 have data
B4 have nul .... etc
i want to display all Column B in Column A one by one to be Organized.
I am trying to sort a table of data by "Status" and automatically populate another Sheet. In Sheet 1, I have data as follows:
Issue Status Issue - 1 Open Issue - 2 Closed Issue - 3 Closed Issue - 4 Open Issue - 5 Closed Issue - 6 Open
I want Sheet 2 to automatically sort this information by Status = "Open".
Issue Status Issue - 1 Open Issue - 4 Open Issue - 6 Open
How I can accomplish this by Excel functions (I do not want to use Pivot table)?
Is there a way to have a reminder/warning message pop up before a user leaves a specific sheet?
I need users to sort a tool list before they leave the sheet and move on to a different sheet?
The name of the sheet is "Unit Tools". The command button is named "Sort Tool List"
I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
View 3 Replies View RelatedHow can I auto fill a range while leaving the cell number the same?
Code: =IF(AND(B4>=c2,b4=c2,b4=c2,b5=c2,b6=c2,b7=D2,b4=E2,b4=F2,b4=G2,b4=H2,b4
I'm trying to sort data on sheet1 and sheet2. I'm running all macros from sheet one and I need it to stay active all of the time. I'm using the following code to sort sheet2. When I sort the column on sheet2 with the code, I can not get back to sheet one without an error.
Worksheets("Sheet2").Activate
Columns("a:a").Select
Selection.sort Key1:= Range("a1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
I've tried
Worksheets("Sheet1").Activate
Columns("a:a").Select..............
We have 2 dynamic ranges (input, output) on different sheets (sample data below).
Sheet1> Input
Dynamic range 5 columns
Sheet 2>Output
Dynamic range 4 columns
- Include only rows IN=1
- Sorted by LEVEL (BIG to SMALL) and BUY (SMALL to BIG)
- Keep duplicates
What formulas should I place on Sheet2 avoiding Pivot tables or VBA?
Sheet1> Input (16 data rows)
A B C D E
Level Buy Sell Firm IN
16620 4.00 null F1 OUT
16610 5.10 0.80 F1 OUT
16600 11.40 6.60 F1 1
16590 24.50 18.60 F1 1
16580 44.90 37.10 F1 1
16570 66.90 59.60 F1 1
16560 84.40 78.70 F1 1
16550 95.00 90.30 F1 1
16540 99.80 95.60 F1 OUT
16530 100.00 98.00 F1 OUT
16611 6.66 0.497 F2 OUT
16600 9.09 1.96 F2 OUT
16589.1 20 12.5 F2 1
16578.2 41.66 33.33 F2 1
16567.3 73.33 65.21 F2 1
16556.4 90.9 84.61 F2 1
16545 98.5 91.66 F2 OUT
Sheet2> output (10 data rows)
A B C D
Level Buy Sell Firm
16600 11.4 6.6 F1
16590 24.5 18.6 F1
16589.1 20 12.5 F2
16580 44.9 37.1 F1
16578.2 41.66 33.33 F2
16570 66.9 59.6 F1
16567.3 73.33 65.21 F2
16560 84.4 78.7 F1
16556.4 90.9 84.61 F2
16550 95 90.3 F1
So I was given the task to translate some procedure instructions in an Excel file from English to Spanish. These are very simple instructions and in most cases repetitive throughout the document. There are a lot of instances where the instructions are the same except for a #. They are meant to be modified within the cell as the text that appears in the cell will be printed exactly as it appears.There are several instructions in the cell, it is basically a long continuous string in the cell. My idea is to create a macro that can search that range of cells for the instruction, replace it with the Spanish equivalent while leaving the original English instruction in the cell.
View 6 Replies View RelatedI need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached.
View 6 Replies View RelatedI would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
View 3 Replies View RelatedHow To Make the Table Sort Automatically Without using Sort Button? For Example, I Changed one of the Cell's Value. Then The Table Sort itself.
View 2 Replies View Relatedi have a load of data that i dump onto a sheet and i would like to sort automatically with out the need to do it via data and sort
I have attached an example
Can it be do within a lookup?
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.
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.
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 11 Replies View RelatedI am trying to get a list to sort automatically. I am running a competition over an 11 week period and entering weekly data that is totalled on a separate "Totals" sheet.
This "Totals" sheet, in turn, feeds a "Scores" sheet which I want to automatically sort in descending order of score in order to show the current positions in the competition.
The data in the list isn't entered directly but comes from formulas that reference cells in other workbooks.
I have found ways of sorting a list when you enter data into the list itself but what I need is a macro that will update the sort order automatically whenever a new value is entered in one of the other workbooks.
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 9 Replies View RelatedI 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.
I have created a validation list which automatically adds any new values a user enters.
Is there any way of sorting the validation list every time a new value is added to the list?
This code sort data of every Column of workbook.
I want sort only range e.g (c10:c61) of sheet(10) correct this code :
[Code] .....
sample.xls
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 2 Replies View RelatedI have a list of Y-intercepts (Column B - highlighted in Yellow) and I'd like to sort it based on a certain value (in J4) and get the 10 closest numbers above that value and the 10 closest values below that value followed by the remaining numbers.
View 7 Replies View RelatedI'm attempting to get a column of due dates to update automatically every time a date changes. The due dates are in B3:B15. There is a header in B3. The day after the actual due date, it is automatically changed to the same day on the next month (showing the next due date). I need the spread sheet to automatically sort the due dates from the closest due at the top, to the furthest due date from today at the bottom. Running it manually, or sorting it manually every time won't work. This is for an elderly couple who are having problems keeping track of everything each month. I just need something that will show them the upcoming bills and how soon they are due when they open the workbook. Anything other than opening the workbook is going to be too complicated. I'm new to macros. I've tried to modify some I've found in other threads, but no luck (lots of error messages). I don't understand it enough yet to figure out what I need to change. I've attached a sample sheet that shows the part of the workbook I'm having trouble with.
View 4 Replies View RelatedScenario: 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.
I have a Spreadsheet with five columns. The column headings begins in rows 3 and 4(merged), these are Reg.#, Rank, First Name, Last Name and Station respectively.
The 5 row is used as a filter row. Therefore my Data Range is from A6:E505. So far I have input some data up to row 25.
Problem: Each time I input data I have to sort manually, I need a VBA Code to paste to automatically sort by Last Name even as I continue to input data in the other rows.
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.
I'm trying to auto sort a list of ranks that are linked to another sheet. My sort code works when it referers to input number values, but when it refers to cells that have formulas, it does not recognize the value from the formula, only the formula itself. Any ideas would be welcome.
View 4 Replies View RelatedI've created this fantasy football programe to calculate all the scores from all matches in this years European Championships.
Basically everybody ranks each team from 1 to 16, giving 16 points to the country you think will win the european championship, 15 points to the runner up, and so on down to the country, you think is the weakest on just 1 point.
Everytime I try and change one of the values in the 'Results' tab I get an "Compile Error/Syntax Error".
What this macro should do is sort all the data in the "League Table" tab into a highscore table list.
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("b1"). CurrentRegion.Sort _
Key1:=Range("h2"), Order1:=xldescending,
Key2:=Range("c2"), Order2:=xldescending,
key3:=Range("e2"), order3:=x1descending,
Header:=xlYes
Application.EnableEvents = True
End Sub