I have this code below which autofilters a range and selects the data i need, there is also a small offset part that leaves the active cell as the one in the top lef of the needed data. I also have the row count of the data in cell G1. What I need to do is something along the lines of:
View 3 RepliesI have a macro that selects and copies and pastes a range of data from one sheet to another sheet. It works perfectly except it is pasting all the cell formatting and formulas and i only want or need the values.
View 8 Replies View RelatedEach month we receive an updated budget which includes info for every month of the budget year. I want the user to be able to select the column (which relates to a month) that they want to use for the analysis. I will use the data in this column only for analysis. How can I do that?
View 3 Replies View RelatedIs this possible? If
A B C
0 0 0
0 0 0
1 4 5
2 4 8
0 0 0
0 0 0
0 0 0
2 6 9
8 9 3
3 5 6
0 0 0
In the raw data, the 0s split up the data into different steps. I am trying to create a function that selects the data from the ABC column and puts it into new columns for each step. I.e:
New Columns with:
1 4 5
2 4 8
and
2 6 9
8 9 3
3 5 6
In the raw data files, there will be 10 steps, with a varying length of data.
2 autofilters i am using, they are both initiated using VBA code and criteria on a control sheet however it appears that one is cancelling the other out, for example i manage to get my date filtered but no times, or if i alternate the run order i get times filtered but no dates i have enclosed the code below ...
View 10 Replies View RelatedI am trying to figure out if it is possible to retrive the list of filter criteria in each filter of the filters collection using VBA. I could trawl through the data and retrive a unique list but I think it would take longer than if I could use what is already there.
I intend to use the list in a combo box elsewhere in the workbook......it's all to stop inquisitive little users accidently changing data and whining that thier figures are misteriously wrong.....
I am using the following code to select rows in a filter.
Selection.AutoFilter Field:=Worksheets(" Dashboard"). Range("G1"), Criteria1:=Worksheets("Dashboard").Range("H1")
This code runs when I click a command button on a userform.
When I load the userform up again I want to clear off the previous filter before I filter again as there are 4 columns which have filters on.
I have tried using ActiveSheet.ShowAllData before filtering again but this code falls over if there isn't a filter already on.
I have searched these boards (and found wonderful ideas), but can't find a better solution.
Is there a quick way to clear all the autofilters on a page (or all of them in 1 row)?
I am using this code, but it is very slow.
For i = 1 To 21
Selection.autofilter Field:=i
Next i
I have an issue where users to a work book are selecting autofilter and then saving the document with rows filtered out, which I wish to prevent or at least warn them to remove the filter before allowing the save function.
The document is never 'Save As', it's always 'Save' from the toolbar icon.
They should be permitted to save the document, but only after this check has been completed and a message issued if the filter is on.
I want to have a small button at the top of the sheet that when pressed, will automatically reset all filtered autofilters back to show all.
View 9 Replies View RelatedI have a vertical list of data which is sorted in to "Regions" 1 to 7 there could be up to 25 instances of each Region, but I have no way of knowing.
I want to be able to select each Region and place it in it's own column.
Is there a way to do multiple case selects. For example if I have A3=2012 and A4=COMPANY NAME do this. I don't want to use if and for this because I have several company names in this list and think case would be easier. So far I have the following attached code but this pastespecial should only occur if the value of A3=2012 as well.
Code:
Select Case Cells(i, 1).Value
Case "COMPANY NAME"
Range(Range("B" & i), Range("E" & i)).Copy
Sheets("Abbott Label").Select
Range("b45").PasteSpecial
i am trying to set up a data validation list for the user to select the options from the 'reason' column' but the input in the box would be the 'code' corresponding to the reason
i.e drop down list would show me the' reasons' but when selected it would input the 'Code' only
Say you have two columns, A & B. Each cell in column A consists of an identical drop-down menu of four possible choices. Each cell in B is a random number. I want to sum the cells in B according to what the user selects from the drop down in column A. If A1=A2="BILL", I want the sheet to add B1 and B2. IF A1=A3="BILL", and A2=A4=TOM, I want the sheet to sum B1 and B3, then sum A2 and A4. So, conceptually, I guess column A acts as a group identifier, and I want to sum B by group.
View 2 Replies View RelatedI have a worksheet with approx. 100 form checkboxes. The cell behind each box holds it's TRUE/FALSE condition, which is something I check for elsewhere on another worksheet.
I need one 'SELECT ALL' checkbox which, when selected, will 'check' every one of these 100 checkboxes (change their conditions to TRUE).
Ideally this would be done thru an excel formula, but I've looked everwhere and not found anything that will permit this.
i am trying to do something along the lines of. a For Next Loop that selects the cell N6 and inserts the formula.
$K$7*F6+$L$7
then fill down to the cell N754
then selects O6 and inserts the formula
$K$8*F6+$L$8
then fill down to cell O754. repeating this until there are no more values in either the K or L column. in the attatched spreadsheet i have deleted the values from 50 something down to 754 but the idea is still the same.
so that each formula inserted keeps the cell from column K and L constant when filled down but F changes, then when the next column is used(ie. from column N to O), the next row in the K and L is used but F starts from F6 and changes as it is filled down again.
I am trying to write a macro, but am failing at it.
Basically, what I am trying to do is:
----------------------
Select a whole row, for example 5
Define that row as "Ceiling"
Then select a row a few steps down, for example 18
Define that row as "Floor"
Then select the range (ceiling, floor)
Then print that area.
-----------------------
But I have no idea how to write the code.
My worksheet with macros gets messed up when a user selects either multiple cells from column F or selects multiple cells in any one row (but only if column F is included) in the range C17:O116 and hits "delete". I will either do an exit sub, an application.redo, or something similar so that this doesn't happen, but I don't know how to write the code to trigger these events. So I need a way to determine "if the user selects multiple cells in column F and hits delete" or "if a user selects multiple cells in any row from 17 to 116 that includes the cell in row F, and hits "delete.""
Set Rng = Range("F17:F116")
Set rng2 = Range("K17:K116")
Set Rng3 = Range("L17:L116")
The code below adds the date to D:D once the user enters data in B:B and then adds the time when the user selects from a dropdown in C:C. When they have finished the task they enter anything into F:F and then it enters the end time in G:G.
This works well except for one flaw and one modification that I can't get to work. I have found that people retrospectively change the activity in C:C which causes the start time to change. I would like to avoid this hapenning if possible by putting some code in to stop this. Also I would like the times to be related to the push of a button on the sheet and therefore need different code.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B2:B900")) Is Nothing Then
With Target(1, 3)
.Value = Date
[Code] ......
A list box selection works fine on my computer but on a friends Windows Vista PC the following happens:
Selecting items near the top of the list works fine. 1/4 of way down a mouse click on an item selects the Next item.
1/2 of the way down in the list, the mouse click on an item selects the one 2 or 3 further down, etc., etc.
I'm wondering if someone can help me with this. I am using the excel calendar control and trying to activate it when a user selects a specific cell "C3". I can get it to activate when I move off the cell, but it's not working when they click on the cell.
Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C3")) Then
Call OpenCalendar
MsgBox "Calendar"
End If
End Sub
Userform2 (I have this code under here)...............................
For example, I have a master sheet that has a drop-down list of the months January and February, which have their own sheets. I am trying to populate the sales of the week 1, 2 ,3 ,4 in the master sheet based on the same data for the month chosen.
View 2 Replies View RelatedI have a userform for inputting information to a spreadsheet using the code below. It works fine however if the user does not select a radio button to select a score its saving the rest without it. How can I ensure the user selects a score,,,, ob10, ob8, ob6, ob4, ob2, obna.
[Code] ......
I'm trying to write a macro that selects cell A1 in each sheet within the book. I've written the following code:
Code:
Sub a1()
For i = 1 To Sheets.Count
Sheets(i).Cells(1, 1).Select
Next i
Sheets(1).Select
End Sub
However, it produce this message: "Run-time error '1004': Select method of Range class failed."
If I slightly modify the code to
Code:
Sub a1()
For i = 1 To Sheets.Count
Sheets(i).Select
Cells(1, 1).Select
Next i
Sheets(1).Select
End Sub
It works fine. I'm curious why this happens.
Whenever she clicks on a cell, the cell to the right of it is also selected. When she tries using tab to move to a new cell, she can only move between the two selected cells. Same with using the enter key. As such, it is extremely difficult for her to modify only one cell, since she always has two selected.
The F8 key, as well as Ctrl+F8. However, pressing the F8 key only adds more cells to the autoselection, and Ctrl+F8 allows her to select one cell, but also highlights the cells around it, and when we tried to select other cells, every cell we clicked stayed highlighted.
Additionally, sometimes when she clicks a cell, it will just select that one cell. Click it again, and the problem is back. I haven't been able to determine any patterns to this behavior, and I know there is no problem with the input (the keyboard and mouse are standard-issue in our lab, and we keep them well maintained).
I have two combo boxes: One for entering the Year, and one for the month. I can produce a message if the user leaves either box blank but I want a message to apear it the user selects a year AND month less than the current year (iYear) and current month (iMonth). I therefore need an AND statement between the two criteria but i dont know how to do it.
'....First Checks the Comboboxes arent blank then below Checks a future month/year secection is chosen
ElseIf YearBox.Value = iYear & iMonthbox < iMonth Then
MsgBox ("You may not enter Data before the current Month")
Else '...... Run main code here
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
I have a macro which I recorded, then modified. The first thing it does is to freeze the top row, then it goes looking for a particular row and inserts some formulae. Nothing complicated at all.
It WAS working perfectly, but I wanted some improvements in the insertion of the formulae. I got that working just fine, but now the panes freeze in the wrong place. No matter what I do, it freezes at cell I16. I want only the top row frozen.
Found this code which was reported to work:
PHP Code:
ActiveWindow.FreezePanes = False Range("A1").Select ActiveWindow.FreezePanes = True
It still freezes at I16. I have tried shutting down Excel, and even my computer, in case it is some weird bug where something is stuck in memory.
I tried recording another macro to format some cells and also freeze the top row. Same result.
PHP Code:
Sub wraptext_top_row()'' wraptext_top_row Macro''
Rows("1:1").Select With Selection
.HorizontalAlignment = xlCenter
[Code] ..........
Same result. I tried copying the contents of the worksheet to Notepad, then into a fresh workbook, just in case there's some weird hangup in formatting that I can't see.
There is NOTHING in the original code that references that cell, or even that row or column. NOTHING. How it got hung up on that one cell I cannot fathom.
The Freeze Panes command works normally if I apply it manually. Any cell, anywhere, it works as expected.
I am attempting to make a range of cells' data be deleted if a cell outside of that range that had a name in it is no longer there. So if I have a list of names in cells A1:A6 and a range of data in cells D10:E20 that corresponds to a name in cell A2. If the name in A2 is deleted I would want the data in D10:E20 to be deleted.
View 9 Replies View RelatedI have a userform with a combobox that has three items to choose from: Blue, Red, and Yellow. When a user selects one of those options, I would like another combobox to appear on my userform with a specific list for that option.
View 2 Replies View Related