Due to the protection function, users cannot sort by descending (using filter), and this is causing some problems. So I was wondering if it were possible for the sheet to be sorted automatically whenever the spreadsheet is opened. Is this a viable solution?
The sheet is titled Summary (Sheet 1), and the range is A4:CI301 and I would like to sort based on the data in column C, in Descending order (C4 - C301).
Like I said, I'm not sure if this is the best method or not, so I am open to any suggestions that may be applicable.
is it possible too sort a page by name with a commandButton?. Here is a example of my project, the address book is sheet3 and I put a button their. I have only 4 columns to sort.
I have several pivot table templates built, and had to add in additional items to the page field. They are now out of order (they need to be alphabetical). If I was starting from scratch, I would just pull the pagefield down, sort it, and add it back to the top. However, since the entire page is built with formulas and formatting and such, I need to use code. SO! Here's what I have so far:
I have a spreadsheet that I am running for world cup score predictions where people predict the result and depending on right users score sheet when I update the actual result workbook it gives them either 3 points for the right result or 1 point for the correct winner or draw of a different scoreline. All this data then feeds through to a league table of the current points.
How I would modify the macro below that when i enter the game scoreline it will automatically sort the league
This macro is currently on the league worksheet but I am guessing I need a macro on the results worksheet as well to activate this
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 2 Then Range("A2:D23").Sort _
I have tried to open an internet page usin excel in order to get some information, for this I wrote the following code which doesn't work:
Dim i As Integer For i = 2 To 3000 Workbooks.Open Filename:="http://www.anm.ro/anm/anm_maint.asp?anmPage=2&ID=i" Application.Goto Reference:="R12C3" Range("C12:C25").Select Selection.Copy ActiveWindow.ActivateNext ActiveCell.SpecialCells(xlLastCell).Next.Select ActiveSheet.Paste ActiveWindow.ActivateNext ActiveWorkbook.Close Next End Sub
How can I make so that, this procedure to open page with ID 2, and 3and so on
However, when this is executed it returns the following error:
Run-time error '-2146697210 (800c0006)': Method 'FollowHyperlink' of object '_Workbook' failed
Clearly, I'm doing something wrong. I wondered if it had anything to do with the fact that the webpage I'm trying to open is a pdf and not html or similar, but this doesn't appear to be the issue.
I am using the below code to login to a website from Excel when I close a userform.
What I now need is a way to check if the website is already open to prevent the code from running and I also would like to navigate to another page on the website once its logged in. The web address of the other page is:
[URL]
Sub LogInToRoyalMail() Const cURL = "http://www.XXXXX.co.uk/" 'Enter the web address here Const cUsername = "XXXXXX" 'Enter your user name here Const cPassword = "XXXXXX" 'Enter your Password here
I want to make Excel show page breaks automatically when I open a new file. Please tell me how to do that. I tried changing the book.xlt and sheet.xlt files, but to no avail. I am using Excel 2003.
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
I have a worksheet with 10 columns, and an ever number of growing rows.
What I would like to do is to Sort Column 'B', along with all the other respective data in the other columns, each time the spreadsheet opens. I would prefer to use VBA or some other auto-launching event.
Sheet 1 is my main page containing all my data, full of formulas and is protected. Sheet 2 is a simple sorting page with no color and is simply for sorting and printing.
Sheet 2 has IF formulas refrencing rows and cells on Sheet 1.
Example Sheet 1 A2= Bob
Sheet 2 A2="" until I put in this formula:
=IF(Sheet 1!A2="","",Sheet 1!A2)
Now Sheet 2 A2= Bob
Simple...
But when I delete a row in Sheet 1 (using my cool new macro...long story)
I get #REF! in the cells on my sorting sheet, as I should I guess, because there is no more row there to reference. It's annoying, but what do I do to fix it without just unchecking the #REF! in autofilter on Sheet 2.
I just want to eliminate #REF!
Do I need to put something else in the IF formula?
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage() On Error Goto ErrorHandl Dim strPage As String Dim webIE As SHDocVw.InternetExplorer Dim myURL As String Dim tableBeg As Long Dim tableEnd As Long Dim RowBeg As Long Dim rowEnd As Long Dim cellBeg As Long Dim cellEnd As Long Dim strBeg As Long Dim strEnd As Long Dim myCell As Range Dim rowNum As Integer With Sheets("INJ") Set myCell = .Range("A2") .Range("A:F").Value = vbNullString rowNum = 2 Set webIE = New SHDocVw.InternetExplorer myURL = "http://www.sportsline.com/nfl/injuries" webIE.Navigate myURL Do Until webIE.ReadyState = READYSTATE_COMPLETE DoEvents Loop strPage = webIE.Document.body.innerhtml...................................
I have a sheet that changes the number of columns based on data from other sheets. This is the only sheet in the workbook that I will print out every day. I have it set right now to fit all columns on one page, however sometimes this will result in the bottom 1/3rd of the sheet not being used. If I use fit all rows on one page, then some of the columns will get cut off. Is there a way to scale in both directions? My guess is there is not, but I thought I would ask because it would work great for me if I could.
I have a report that I use and right now I have a command button that prints the report x2. What I would like to do is to have it print one in color and one in grayscale. I could do this I think with two different buttons, but I would like to utilize just one. I tried creating a macro but it did not pick up the fact that I changed the properties to black and white.
I am looking for a formula that will take data from one page and graph it on another page. The data I am trying to graph is arranged like so:
A B
1 q 2009.05.01 2 w 2009.05.02 3 w 2009.05.01 4 q 2009.05.01
I am looking for something that will look at column A and if the answer is "q" and then look at column B and if the answer is "2009.05.01" Excel will take that and count it in a specific cell on another page. In the case above, I want Excel to give me the answer of "2" in a designated cell.
Could I use 1 multipage UserForm Instead of 8 UserFoms. Is it better to use 1 multipage UserForm with 8 pages or 8 UserForms. I dont know if this make sense or not?
I have a workbook that has 9 different tabs in it. Once all the information has been completed throughout the workbook, is there a code that could transfer specific data on each page to a different colum on that same page? I need to do this for 9 pages at one time. The information is listed differently on each page. I need to transfer data from the current data column to the previous data column once the entire workbook has been filed out.
how to get rid of the page numbers that appear when using page break preview mode. The page numbers appear in large grey font in the middle of each page and sometimes makes it difficult to read cells. Can I use this view with all the same functionality without that one feature?
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
Working with 2000 Excel is bringing some new chalanges. I see things are done different. I am trying now to keep my header to repeat from page to page.
For example I have a header that may say something like: People, numbers, date, ect. And there are so many new rows they go on for pages long. How can I keep my header on the top of each page when it prints out on paper.
Assume that calculation is set to automatic and not manual, then is every formula on a page updated when any value on the page is changed, or only when the values in the cells pertaining to a particular formula change?
For instance, if cells a1-a10 have values and in a11 is a sum a1-a10 formula, and in b1-b10 are values and a sum b1-b10 formula in b11, if I change a value in range b1-b10 but do not change any values in a1-a10,then as well as the formula in b11 updating will the formula in a11 recalculate or does excel keep track of which cells have changed and thus is aware that the formula in a11 does not need to be recalculated?
What I want to do, is to populate the Lois, Beth, Kelly and Shelley pages with the rows from the Que page when they get assigned to one of them. I tried to program then off of a different example in the forum. It didn't work. Will include the copy.
In my header I am trying to show the current page number and the total number of pages in the workbook. So if I have 10 pages in my workbook, page 5 would read "5 of 10".
I have this in my header "&[Page] of &[Pages]". All worksheets with 1 page read "1 of 1" and worksheets with 2 pages read "1 of 2" on the first page and "2 of 2" on the second page.
I have a list in rows where I have a ranking formula =COUNT($G$5:$G$81)-(RANK(G5,$G$5:$G$81)+ COUNTIF($G$5:G5,G5)-1)+1 When I sort the rank, ascending. All of the unique numbers sort ascending, but the non-unique numbers sort descending
