Posts by Jaffey

    Anyone know of a way to switch the default positioning of pivot table row totals? I would like to left justify them insead of always having the totals on the far right side where they are often out of view to the user without scrolling. I end up having to place 2 pivot tables side by side so that I can make the totals appear first. (hope this is making sense to you!)

    My goal is to loop through all the pivot tables in a workbook and update the source data range for each one. Some worksheets have several pivot tables on them. The following code which I borrowed from various posts loops through the sheets but only updates the first pivot table on each page. It also crashes occasionally with the error "A pivot table can't overwrite another pivot table" on sheets that only have 1 pivot table on them. (I swear!)
    If anyone could help me fix this so that it updates all the pivot tables on each worksheet I'd appreciate it very much.

    Re: Paste Special Link & Transpose

    I added ".Cells(1,1)" as suggested but the result is the same. While also trying to control the formatting I noticed that if I add PasteSpecial Paste:=xlPasteValues before or after the transpose command it affects the direction in which the cells are transposed, to the right or up and down while coping the cell contents in the opposite direction. I know I'm probably not making this very clear so I've attached an example of my goal and the two different outcomes I am getting with this code depening on which line is commented out.

    With Selection.Cells(1, 1)
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial transpose:=True
        '.PasteSpecial Paste:=xlPasteValues '
        ActiveSheet.Paste Link:=True
    End With

    Re: Paste Special Link & Transpose

    That works great! Thank you very much for showing me how to combine the two functions. I was, however, hoping I could find a solution that works with any selected range instead of a hard coded one. I tried modifying your code to work with a range already copied to the clipboard but it doesn't work, it paste/links the first selected cell correctly but instead of giving me the rest of the range I had selected it links the cells transposed to the first cell. Ex. If I copy A1:A5 and then transpose/link using my modified code, it transpose/links A1:E1 instead. Similarily, if I copy range A1:E1, is transpose/links A1:A5. Can this code be modified to transpose/link the original range selected instead of transpose/linking the cells beside or below the 1st cell I selected?

    With Selection
        .PasteSpecial Transpose:=True
        ActiveSheet.Paste Link:=True
    End With

    Is there a simple way to combine Paste Link and the Paste Transpose function? I receive a lot of data in rows that I need to put in columns for display purposes. Using transpose is great for doing that but I would like to link it using paste link so I don't have to keep doing each time the data changes. I have recorded macros to see what is happening when I perform the operations seperately as shown below but have been unable to combine them.

        Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=True
    ' need to combine with this
        ActiveSheet.Paste Link:=True

    The following code was supplied by SHG in response to a previous post. It works great but when I run the code the output overwrites any existing output. I would like to modify it so that it appends the new data to the end of the existing output. (I should have said that in my original request) I have tried searching for the answer myself but all the examples I've found are based on appending via the paste command. If someone could please tell me what to add to the following to make it append the output I would appreciate it very much.

    Note the original post was: Parse Name & Number From Text String

    Re: Parse Name & Number From Text String

    I am getting out of range errors with or without [b(n, 3) = "no hit" ]
    Actually I am getting out of range errors with both Bill's and jindon's suggestions but shg's earlier modification to his solution appears to be working fine. The increasingly simple and yet dense logic of your code is inspiring but i must admit it is taking me longer and longer to figure out what it is your doing. Good thing it's the weekend! Cheers and thanks again!

    Re: Parse Name & Number From Text String

    Honestly your code is beyond me. I've recorded and edited countless macros but really I'm just a hack. I have been using spreadsheets for many years but only recently began delving deeper into real VB. I am trying to work my way through your solution but it will take me a while to understand it. I just learned about intersect last week!

    Re: Parse Name & Number From Text String

    Hi Shg, sorry for the delay in responding. This is AMAZING. It's giving me some errors but this is a huge start. The errors I'm seeing seem to be related to the records with no score. When a record has no score it is appearing twice in the output, once as a no-score and then again as having a score which is actually the score from the next record. The next record is then being left out of the output. I have attached a larger sample with your code inserted so you can see what I mean. Thanks again!

    I receive a text file daily of between 100 to 50,000 rows. It is a combination of many smaller text files or "records". Each record contains a row containing the name and some particulars . From 1 to 5 rows below that row there may or may not be a row containing the score for that record.

    Here is an example of what the rows look like;

    4505329 64036593 150090 MS MARY SMITH AB Finished
    CRP 3.0 SCORE: 400

    From the first row I need to pull out the second string of digits (ex 64036593) and the person's name. From the second row I need to extract the score (400). I would like to copy the three pieces of information to 3 separate columns on another sheet.
    1. the word "Finished" always appears at the end of the row with the name in it but it also appears at the end of every record in it's own row like this;
    Finished No Note
    2. the strings of digits in the first row can vary in length but there are always 3 of them and they are always seperated by spaces.

    I need to extract all the names and scores and put them in a table. If a name is not followed by a score I need to put "no score". Any help is greatly appreciated!

    Re: Pick Top x Using 1 Column

    That worked beautifully Domenic. I had to tweak it slightly because the dates are not always sequential (we're closed Sat/Sun) but other than that it worked perfectly. I've never seen that trick for displaying a specified number of results before either so thanks for that as well. Have a Happy New Year!!

    Re: Pick Top x Using 1 Column

    I'm sorry, my request was obviously not clear enough. I need to extract the top 10 agents for the day based on the the revenue they brought in yesterday, not their MTD grand total. When I said I need to find the top 10 values in the right-most filled in column I was talking about the daily revenue column that is filled in each morning with yesterday's results. I have no difficulty selecting the top 10 for the month from the MTD column, it is selecting them from the individual daily columns that I am having difficulty with. I need to find the top 10 values in the next daily column each day as it is filled in. Sorry for the confusion.

    Our accounting dept. has been using the attached spreadsheet to track daily revenue for years. I plan to migrate this process to Access but for now I have to work with this. My current task is to come up with a daily Top 10 list of the 10 agents who posted the most money for the day. The attached table has the agent names in column A, their quota in column C, and the days of the month are columns E to Z. Each day the accounting dept. fills in each agents daily revenue in the corresponding column. How can I extract the top 10 values from the right-most filled in column and place them on a report page? Bearing in mind that there will be gaps in the data (blank rows), possibly even blank days (blank columns). Also, in order to qualify for the top 10 list, an agent must have a minimum quota of $2000. I would also like the name of the agent and client they work (column B) to appear along with the revenue amount. Ex.
    Smith, J Citibank $1,000
    Brown, B Amex US $957
    Green, S CHase $850

    I use variations of some code I found in another post to display user and computer names.
    I would like to be able to display just the first name of the user. Can anybody tell me how to do that? I am able to use the LEFT function to display a specified number of characters but that isn't a very good solution obviously and I don't know how to break on the space between the names. As always, your help is greatly appreciated!

    Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1).Value = Environ("ComputerName")
     Sheets(1).Range("b" & Rows.Count).End(xlUp).Offset(1).Value = Environ("UserName")

    I am using the following code to insert pagebreaks based on changes in column D. I would like to change it so that page breaks are inserted after the appearance of the word "Total" in column D. Note that the word total will typically be preceeded by other words. Example "Sales Total" or "G. Smith Total". If possible, I would like to suppress this logic when the words "Grand Total" appear as I would end up with a grand total page with nothing else on it. Many thanks!

    Re: Check If Active Cell Is In Specified Column

    In that case I need it to skip the last line "open-audit_file" or it will start the next macro so I changed it to

    Sub AUDIT_FILE()
        If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Then
             MsgBox "Select an account in Column A and try again"
             GoTo 1
        End If
        If IsEmpty(Sheets("Credentiales").Range("A1")) Then ufCredentiales.Show
    End Sub

    and now it exits completely if they are in the wrong column. This is definitely better than pausing anyway. Thanks Turtle44! Thanks Stan!