SortSheets Macro For Statement Does Not Run Correctly
Jul 15, 2006
i have tried 2 different ways of sorting my worksheets in alphabetical order. Each time i try these it does something to all the sheets where it's not letting my For statement to run correctly. It was running correctly before I added the Sort Sheets macro. My workbook has a bunch of sheets for every agent and one summary sheet. The for statement looks in A1 for the condition. the condition is in every sheet except for the summary sheet.
Here is my For statement. Its was looking in A1 for the criteria before and working fine but for some reason it now is finding the criteria in the "summary" sheet when there criteria is not there?!?!
Sub run_it()
If Range("A8") <> "" Then
Range("A8").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
End If
Dim month As Integer
Dim month1 As String
month = InputBox("Enter the MONTH (Number) you are reporting")
If month = 1 Then
month1 = "January"
ElseIf month = 2 Then
month1 = "February"
ElseIf month = 3 Then .............
View 4 Replies
ADVERTISEMENT
Oct 22, 2009
I need the macro I created to run (when assigned to a button) in specific sheets in the workbook, additionally if the workbook is saved under a different name by the user, the macro will continue to work. There are 10 sheets. I need it to run in sheets 6-10, when the button is clicked in sheet 5.
Then end goal in mind is to assign this macro to a button, so the user ,when ready ,can click the button to produce the results.
Background & Current Results :
My macro is simply an advance auto filter that copies the filtered data from sheet 3 to sheets 6-10 (non-unique values).
When applied per sheet , it works . When I modify the macro (I've tried several ways based on info published here) , the best result i can get is the data copies 6 times in the same page ( whatever active sheet is selected.). I have not assigned the modified macro to any buttons, why bother if I can't get it to run in the 1st place .
I suspect I need to redo the whole thing to have it simply copy the filtered data repeatedly to sheets 6-10 , but I am not that savvy to figure that out .
My Current Macro that Works Per Sheet
Sub tcktoriginal()
'
' tcktorginal Macro
' macro to run tickets
'
Range("F4").Select
Range("cpt.code.table.").AdvancedFilter Action:=xlFilterCopy, CriteriaRange _
:=Range("A1:S3"), CopyToRange:=Range("A11:S11"), Unique:=False
Range("F5").Select
End Sub
Examples That I have Tried and Didn’t Work ........................
View 9 Replies
View Related
Jan 16, 2009
I'm attempting to create something that can be used on any workbook open to automatically open a file based on the data in cell.
1) User right clicks on a cell.
2) If the cell's value matches an expected format (##-A-####-## for example), a macro is available in the rightclick popup menu.
3) The user selects the macro in the popup menu if they want to run it.
4) The macro takes the cell value and converts it into a filename.
5) The specified file is opened in an external program.
View 4 Replies
View Related
Jul 12, 2009
I am putting together multiple worksheets with dumped data that should sort themselves by the press of a button. Each entry has a 'code' and a value and they are sorted by the 'code'.
At the moment i have the first worksheet sorting correctly and i am trying to program the second worksheet to sort data into the existing worksheets if they exist or create a new worksheet if the data doesn't have it's own worksheet.
Here is the code i am working with.
View 12 Replies
View Related
Mar 16, 2007
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?
2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View 2 Replies
View Related
Jan 11, 2010
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.
I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'
After = Before values (in this mock)
When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.
View 3 Replies
View Related
Nov 4, 2009
I am a teacher in London and have set my class a problem and I am not sure if it can be solved the way I thought (Duh!)
They are designing an ATM screen that moves on to another worksheet/screen according to whether a password is correct or not.
View 14 Replies
View Related
Mar 5, 2009
is it possible to write an if then statement in a macro (in Excel) in order to run a specific portion of it. I.e. If C4="blank" then continue running the macro, if not skip to a lower portion of the macro.
I'm attempting to write a macro to format specific sections of a generated output from another system. The issue is that there could be 2 sections or 75 sections, so I would have to write the if then to skip repeating the macro and skip to the end, which would format the footer, once it hits a specific blank cell 2 lines below the last section it formatted.
View 14 Replies
View Related
Oct 28, 2013
I need to run only one of three Macros depending on the contents of an individual cell. Logically speaking, if cell a1 is "YES" , run Macro 1 which will unhide worksheet1, If it is "NO" , Run Macro 2 which will unhide worksheet2, and if it is N/A, run macro 3 which will unhide worksheet3.
Is "IF" nested the best approach for this ?
View 3 Replies
View Related
Jul 28, 2008
Is it possible to run a macro from an IF command?
View 9 Replies
View Related
Dec 10, 2008
I am trying to write an if statement in my macro. It is not returning what I want. Can anyone tell me where I have gone wrong.
Dim D As Variant
D = Sheets("Instructions").Range("n3").Value
If D = 1 Then
'range("n5).value
Else
'range("b3").value
End If
View 9 Replies
View Related
Nov 19, 2013
I want to use wildcards and it seems I cannot do it with case select, is it possible?
Its required because for the list of PSV's I have more than 2000 names and I would like to use *PSV* for them all instead of typing them all.
Here is the code below:
Option Explicit
Sub Colorize(Rng As Range)
Dim Cel As Range
Application.DisplayAlerts = False
Application.EnableEvents = False
[Code] .......
View 4 Replies
View Related
Feb 11, 2014
I'm having a bit of trouble with a macro designed to read the length text in a cell and if it = a certain length then perform an action (in this case Text to column)
Here is a small sample of the data I'm working with:
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
Tue 02/11/2014
LastBootUpTime
Tue 02/11/2014
LastBootUpTime
20140211082244.222441+000 <<
*End Sample*
The text length I want it to perform the action on is highlighted with "<<" if the length of text does not meet the required number then I want the statement to skip and move onto the next one.
I have the text to column code already done with relative references however the long text string I want the statement activated on is not always present which means that the pattern (0,3) is not always consistent.
View 3 Replies
View Related
Nov 21, 2007
In cell M32 there will either be an O or the cell will be blank. In cell T51 I had placed an IF statment like this:
=IF(M32="O","","X")
Now that works ok but there is a glitch on my part. If T51 is blank because of M32 containing an "O" then the user will also enter a number into T51. Because I was using an IF formula in the cell it gets overwritten because of the user needing to enter data in the cell because of M32 containing a O. How do I write a macro so that a similar formula is used but still able to enter data manually without overwritting the formula?
View 14 Replies
View Related
Sep 21, 2009
I recorded a macro (which worked), then tried to get more out of it by adding code to add a further function (which didn't work). The code is as follows:-
View 3 Replies
View Related
Feb 2, 2007
How to stop macro while running SendKeys statement. I am not able to stop the process while running SendKeys statement. this is my Sendkeys VBA
For Each cell In Selection
SendKeys cell.Text, True
Next cell
View 3 Replies
View Related
Aug 14, 2007
how to use some form of the saveas dialog within excel without quotes appearing in the filename that is passed to the dialog.
For example:
ChDir ("J:TestFolder")
x = Application.GetSaveAsFilename("Test.xls", "Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title")
The above code displays the saveas dialog form but has "Test.xls" surrounded by quotes. Normally, there would be no quotes around the specified filename.
Is there another way I can do this or will I just have to live with it?
View 3 Replies
View Related
Aug 27, 2012
1) I have a VBA macro MyMacro() which works fine.
The macro is assigned to a button on the w/s, and also runs from w/b Open() event and other locations within the w/b.
2) The macro displays a message just before exiting whenever it is called.
3) I’d like the MsgBox statement in the macro to be executed ONLY when the macro is run from the button.
In other words, skip the MsgBox statement when the macro is run from anywhere else in the VBAProject.
Can this be done ? possibly by inserting a statement or two before MsgBox in the MyMacro() code ?
View 4 Replies
View Related
Mar 3, 2014
I should i make this formula
A customer is eligible for a discount if the customer’s 2009 sales are greater than or equal to 200000 OR if the customers First Order was placed in 2012. " If its true it would be yes and if false no"
So far i have in the IF function
=IF([@[2009 sales]]>= 200000 ,"Y","N")
How should i insert if the customers OR First Order was placed in 2012?
View 2 Replies
View Related
Jan 24, 2007
I have used the Excel program for a number of years to print out "Jukebox Title Strips" for my Jukebox. I have used the same "formula" for cell height, width, etc. during that time.
The outline of the title strips are preprinted and perforated on an 8.5" x 11" sheet. I recently tried to make some new strips and the printing does not line up correctly as it always had done in the past. Now, the titles are not high enough on the top portion of the sheet, and not low enough on the bottom portion of the sheet. So, it is not that I could just raise everything up or lower everything down to line up correctly... it now has to be changed in both directions. Of the 20 title strips on the sheet, the only ones that line up correctly are the two that are halfway down.
I thought that somehow the file had become corrupted, so I made a new template, but I still have the same problem.
Are there any suggestions of what I could do?
In the links below it shows an example of a Title Strip page.
View 14 Replies
View Related
Jul 31, 2013
I have three columns expressing death dates (date, month, year). The months and dates are finally sorting correctly within the same year, but I have it set to sort by year first.
The column starts off fine with 1860, goes up to 2013, then starts back at 1884 and goes back up to 2009. I can't find any difference between the two groups: there are some of the same years represented, there are blank month and date cells in both. I have a worksheet with birth dates that sorted fine; the only difference I can find is that birth dates only go up to 1997, but the column is formatted as General, so the turn of the century shouldn't be a problem.
I've tried highlighting the whole thing before sorting as well as selecting one cell as usual. I've also tried formatting the column as Text. I can't think of anything else.
View 14 Replies
View Related
Jan 29, 2009
I have built a UserForm (Excel 2003) that looks up a Row dependant on Criteria and displays the values on the User Form.
View 9 Replies
View Related
Jul 21, 2009
I have a userform with several inputs which are validated, and should the input be invalid an ErrorProcedure is run. This basically displays a message telling the user what is wrong, and it should also be highlighting the text in the relevant field, but for some reason it is not doing that.
View 6 Replies
View Related
Sep 27, 2012
Linking two comboboxes. Actually My department consists of 4 divisions and each division contains 3 sections, that is beside Department manager staff and planning group.
In the attached workbook:
- Combobox1 contains All divisions, Department manager staff and planning group)
- Combobox2 contains all sections of the selected division in Combobox1.
What I want to do is the following:
In Combox1, I want to hide all records not related to the selected division.
In Combox1, I want to hide all records not related to the selected section.
Remarks:
I depend for filtering on the org. code for each division and section.
Fifth and Sixth digits of the first Division's Org. Code are 21
Fifth and Sixth digits of the second Division's Org. Code are 22
Fifth and Sixth digits of the third Division's Org. Code are 23
Fifth and Sixth digits of the third Division's Org. Code are 24
First section's org. code for each division ends with 11
Second Section's org. code for each division ends with 21
Third Section's org. code for each division ends with 31
I have created the following VBA codes to reach the said purposes, the first three ptions in Combobox1 work just fine, but the other options sometimes work fine and other times don't!
Moreover, Combobox 2 is not working at all:
Code:
Private Sub ComboBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False
Select Case ComboBox1
Case Is = [AC4].Value
[Code] ........
View 1 Replies
View Related
Oct 23, 2007
I have a column with a value resulting from a fairly complex formula:
I will not include the formula, but basically what it does is a number of calculations including subtracting dates, that is
TODAY'S DATE - A GIVEN DATE (provided in a different cell)
That means that, either manually or automatically, those values are constantly recalculating (with every second that goes by, that value changes).
And the number of values/cells to update is not few: about 1100.
Here's the problem.
When I press SORT, Excel seems to be absolutely incapable of sorting that list correctly. The values are completely and randomly out of order.
I wonder if it could be due to the fact that given that those values would be constantly changing, there would be a conflict with the commands
sort
update
View 9 Replies
View Related
Dec 8, 2008
I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it.
Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...
View 9 Replies
View Related
Jun 11, 2009
I've got a text box with some commentary in the middle of a spreadsheet. The text looks fine on screen, but when I print, it chops the end off some of the words. Is there a way round this?
View 9 Replies
View Related
Sep 12, 2006
I have a userform with a combo box that is populated from a list in a sheet called Clients. The userform is in the same workbook as the Clients sheet. This workbook called "Client List.xls" and is set to be hidden from view. I've created a menu bar icon that loads the "Client List.xls" workbook, and on open runs the initialize procedure. The problem seems to be caused by the "Clients List.xls" being not the active workbook. Is this the case? Is there a way around it? If I unhide the workbook and make it the active workbook, the code works great.
Private Sub UserForm_Initialize()
Dim rngClients As Range
Dim wsSheet As Worksheet
Set wsSheet = Workbooks("Client List.xls").Sheets("Clients")
With wsSheet
.Range("a65536").End(xlUp).Select
.Range(Selection, "A1").Select
End With
Set rngClients = Selection
With cmbClients
.RowSource = rngClients.Address
.ListIndex = 0
End With
View 3 Replies
View Related
Oct 31, 2006
The relevant code is below. I can post it all if necessary -- it's about 30 lines though. Can anyone recognize what the problem might be?
MsgBox "vSh " & vSh & " vOp " & vOp & "vCash " & vCash
vCash = vSh * vOp
MsgBox "vCash " & vCash
The MsgBox's are merely for debugging. When the code above runs, the first MsgBox I receive states similar to:
"vSh 14.238964 vOp 45.23 vCash 1000"
The second MsgBox, from after the multiplication, states:
"vCash 1000"
For some reason the vCash variable just will not set. At the beginning of the code, I declared each variable as Dim var, meaning as a variant, and whenever I debug by halting during a MsgBox display, each of the variables appear to be the same types: Variant/Double.
View 6 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
