I have a Ranking tab that has the persons name, weeks, months that subtotal into 2 columns, MTD and YTD. I have set up with 2 Macros to sort based on two columns based on their selection of MTD or YTD.
What I did not take into consideration is if a person was added at the bottom of the list.
Is there a way to modify this to include rows that may be added?
VB:
[SIZE=4]CODE HERE[/SIZE]
VB:
[B]Macro1
[/B]Sub SortYTD()
[Code].....
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
How might the code below be changed to handle any number of rows? Right now it's set (from a recorded macro) to go down through row 237, but I need it to sort anything from just a few to maybe a thousand.
Cells.Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C2:C237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("B2:B237"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range("K2:K237"), _
[code]...
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
Essentially i get a Runtime 5 error on the bold bit of code....
I am trying to define a range .... by using thexlUp function find the last row ( which works ) i then pass this variable into the sort code and get the error its probably very simple to fix, all it does i sort columnA but finds the last cell .... instead of the whole column,,,,
Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+e
NumberOfRow = Sheets("Sheet1").Range("A3000").End(xlUp).Row
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Sheets("Sheet1").Range("A2", Cells(NumberOfRow)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
[Code] .......
I have a list of letting agents that I need to sort into a table to import into Access. The list is dynamic in that not all the fields are present for every record. I will have over 4000 records to sort out, so I would really like to automate this. If possible the macro should read rows from the data worksheet and write to columns and rows on the Table worksheet. I have attached a small example of the data and output required. To make it a little more challenging the column header name is part of the data and will need stripping out as well.
View 3 Replies View RelatedI am trying wihout suceess to create a range to copy in a macro each time the macro loops. This is thus a dynamic range. My failed attempt is listed below. How is this done?
ColumnAdjust=2
For I = 1 To 282
Range ("B5").Select
Range(ActiveCellOffset(0,ColumnAdjust),ActiveCellOffset (0,ColumnAdjust+1))
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
.
.
.
ColumnAdjust= ColumnAdjust=2
Next I
I am attempting to create a dynamic range within a macro.
Each week, a list of performance factors is recorded for employees. The macro is about to do an Advanced Filter to create reports based on user-provided criteria. Since there is new data every week, the range used for the Advanced Filter is dynamic. The code for the Advanced Filter refers to a range entitled “Database.” My problem is in defining “Database.”
It is currently 534 rows and 8 columns, starting at A1.
The relevant portion of the macro currently reads:
Sub Check1()
ActiveWorkbook.Names.Add Name:="Database", RefersToR1C1:= _
"=OFFSET('Loan Officer History'!$A$1,0,0, COUNTA('Loan Officer History'!$A:$A),8)"
Range(Database).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"J1:Q2"), CopyToRange:=Range("J5:Q5"), Unique:=False
End Sub
When running the macro, the debugger stops at the line which starts "ActiveWorkbook.Names" and tells me “Run-time error ‘1004’. The formula you typed contains an error.”
I am trying to write a macro to sort a range of data by another column. Here's the code that I already have :-
Code:
Public Sub sort_data(sort_range As Range, key_range As Range)
' MsgBox sort_range.Address
' MsgBox key_range.Address
sort_range.Sort Key1:=key_range, Order1:=xlAscending, Header:=xlYes
End Sub
However I get a run time error 1004 stating that the sort reference is not valid.
I recorded this macro based on a column with a drop down window in which data has been filtered .....
View 12 Replies View RelatedI need a bit of assistance in identifying the dynamic range so that my macro below will work:
View 3 Replies View RelatedI am still having some difficulty in getting a macro to select a print range on a text file imported into excel. All the data pulls into column A which is OK. I'm trying to get my macro to highlight & select the print range by going Home, End Down, Tab right Tab right and then select whatever that highlighted range is. This range will change from file to file.
My macro reads as follows:
Sub PrintToView()
'
' PrintToView Macro
' Macro recorded 5/5/2004 by
'
'
Range("A1").Select
[code]....
I am looking to run a marco for a selection range, and the selection range could vary in size.
Using the Macro recorded (whilst turning on the relative reference) the Macro runs for a defined number of cells. I would like to run certain Macro, for different ranges of cells.
I am not sure if my problem is in the dynamic range or not. I have attached a small sample file. I tried to create a dynamic range by creating a named range of my "LabelRow" with the following as the refers to: =OFFSET($C$32,0,1,0, COUNTA($32:$32))
When I set my cursor on this, Excel makes an outline on the whole row 32 where there is data, which gives me some clue that it understands what I am trying to define. Next, I try to pick this range up and paste it using named ranges in a macro (CopyRange). It gives me an error like it doesn't understand the named range. I do similar macro code in many other places in this project and it works with non-dynamic ranges. Here are some other details on this project. I need a dynamic range because I insert new data into the dataset, which may be 2 quarters up to 20 quarters. This insert causes a regular range to be messed up. My ultimate goal is to graph selected rows of data with the same labels, but I need to have it starting at Q1, which is pushed to the right as new rows are added.
I have a named range that is being updated via a macro. After the update, I want the list to be sorted. The named range is "Projects". This name is not a variable...it is a named range within Excel itself.
Any code to sort this at the end of my existing macro.
I recorded this macro - which was a simple copy and paste and then sort the results, however it works in excel 2007 and not in 2003. Even tried to record the same in excel 2003 and it still does not work. It seems to fail at the sorting stage
Sub sortprices()
Sheets("Rates").Select
Range("B229:C241").Select
Selection.Copy
Sheets("Results").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("D4:D16") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Results").Sort
.SetRange Range("C4:D16")
. Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("C4").Select
End Sub
I have an Excel sheet that lists all of my dvd collection.
Its very easy to use, all I need to do is type the movies I have then click a sort button.
The everything goes into alphabetical order.
The problem I'm having is it only goes to 999 I have tried to extend it to 3000.
I can't seem to find the formula to allow me to extend the perimeter past 999.
I am trying to select rows in excel using a macro and then hide the rows, but i want to be able to add rows within this selection and still be able to hide the complete selection of rows.
The macro i am currently using is shown below: This hides rows 131 - 205, but if i was to add in another row in between so the range i now want to hide is 131-206 can i get the macro to update to this new range?
[Code] ......
I have been tring to define a dynamice range in VBA. At the moment, I use following method to Define the range,
Sub OTC_Future_Total()
Dim DynamicRange As Range
Worksheets("FinalReport").Select
Range("e9").Select
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select...
I am copying and pasting from two different " timesheet" spreadsheets into a list. One of the timesheets has blank rows. I am attempting to sort the blank rows to the bottom after I paste the data, but every time I do, it either replaces the top row with "true" or deletes the headers,
Sub SortBlankRows()
Dim rngCurrent As Range
Dim c As Range
Dim inUsedRow As Integer
Set rngCurrent = Workbooks("Payroll Summary.xls").Worksheets(1).Range("A1:J1")
inUsedRow = Workbooks("Payroll Summary.xls").Worksheets(1).Range("D65536").End(xlUp).Row
rngCurrent = rngCurrent.Resize(inUsedRow)
rngCurrent.Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("F1") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub
I need to find average of the values , the count of the cells will be dynamic (may be 5 or even 200).
View 2 Replies View RelatedUltimate goal is to automatically update the source data for 4 pivot tables that are on 1 worksheet. The data for those 4 pivot tables are on 4 different worksheets w/in the same workbook. Consolidating into one dataset is not an option.
I'm familiar with a dynamic named range, but the 4 worksheets that contain the data are replaced daily via automated Access output which creates an error.
I know how to do this adhoc by matching the pivot table names with their respective worksheets, but there are many other documents with similar layouts where this would beneficial.
Below is an example of how I currently update 3 pivots on separate worksheets w/ the same data range which is w/in the same workbook. My proposed changes are below the current. Any ideas on how to return the workbook name as a string...Or am I going about this the completely wrong way...
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] .....
I have a spreadsheet that ranks the 500 components of the S&p 500 on an ongoing intraday basis. I would like to have the sheet sorted automatically as it is updated. I have very limited VB skills. Also, I heard there is a feature on excel 2007 that performs this function.
View 7 Replies View RelatedI have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I currently have two tables in one worksheet showing the sales of different region.
The problem is, when I sort the data in the table (I can't used the named ranges as it should exclude the first row which is the header), is there a way I can make it dynamic too??
**Attached, please find the example spreadsheet, I have only written the code for the first table.
I'm trying to write a code to sort a variable-sized data range on several sheets. A sql query populates several sheets with data in N rows, where N varies based on query parameters).
I get a run-time error on the .sort command (method range of object global failed). i think this has to do with sorting by the same column on multiple sheets and haven't been able to debug it myself.
Sub sort()
Dim lastrow As Integer
Dim rows As Integer
Dim sheetarray As Variant
Dim colarray As Variant
Dim i As Integer
am working on a spreadsheet that requires sorting a list of names and numbers. The numbers have a sum formula attached. I can set up the macro to sort the list fine by selecting the appropriate cells and creating the macro.
The code for this is below
Sub SurnameSort()
'
' SurnameSort Macro
' Sorts by mechanic surname
'
'
Range("A10:H13").Select
Selection.Sort Key1:=Range("B10"), Order1:=xlAscending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
However, I want the macro to still be able to sort the data when I add new rows to the list later on. In other words, the code above in bold somehow needs to be modified so that "H13" extends as far down as needed.
I need to create a BOTH box that will dynamically pull names from both the "Chicago Office" & "Seattle Office" columns in my sheet. It is extremely preferable to do this without VB Script if possible to avoid the security warnings on opening the sheet. (Our IT department will not budge on this..)
Detail:
I have two lists of employees. Column A lists the Chicago Office employees. Column B lists the Seattle Office employees. I've been able to successfully define named ranges to work with these as dynamic lists. I can append names, or delete names, and the Chicago, or Seattle boxes (drop downs created with data validation formulas inside named ranges) will reflect the updated names correctly. I have been unable to make a single drop down with all the names from both offices, that is updated dynamically. I've attached a sheet so that this is easier to understand. Basically I need the "BOTH" drop down to actually work.
The Chicago range is defined as:
=OFFSET(Sheet1!$A$2:$A$11,0,0, COUNTA(Sheet1!$A$2:$A$11),1)
The Seattle range is defined as:
=OFFSET(Sheet1!$B$2:$B$11,0,0,COUNTA(Sheet1!$B$2:$B$11),1)
In the boxes on the right of the lists, I just have a Data Validation List formula as:
=Chicago and the other as =Seattle
I 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 Related