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.

Sub AllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet


        For Each ws In ActiveWorkbook.Worksheets
        For Each pt In ws.PivotTables
                      
        ws.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
        "'M:\AMEX_CAN\[qryAMEX_CA.xls]qryAMEX_CA'!$A$1:$BK$10000"
         
    Next pt
    Next ws
    End Sub

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 '
    .Activate
    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
    .Activate
    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.

Range("A44").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True


' need to combine with this
    Range("A44").Select
    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.

Sub Jaffey() 
    Dim astr()  As String ' parsing buffer
    Dim iUB     As Long '   upper bound of
    Dim iStr    As Long ' index to
     
    Dim rOut    As Excel. Range ' output range
    Dim iRow    As Long ' row index to rOut
     
    Dim cell    As Excel.Range 
     
    Dim sName   As String ' account name
    Dim sAcct   As String ' account number (string to preserve leading zeros)
    Dim bName   As Boolean ' notes last notable item was an acct/name row
    Dim vScore  As Variant ' score
     
    Set rOut = Range("C2:E2").Resize(, 3) 
    rOut.Resize(Rows.Count - rOut.Row + 1).Clear 'note to SHG, I can delete this line and it still works fine as far as I can tell
    rOut.Rows(0) =  Array("Acct #", "Name", "Score") 
     
    For Each cell In  Intersect(Columns(1), ActiveSheet.UsedRange) 
        astr =  Split(WorksheetFunction.Trim(cell.Value), " ") 
        iUB = UBound(astr) 
        If iUB > 0 Then 
            If UCase(astr(iUB)) = "FINISHED" Then 
                vScore = "no score" 
                If bName Then GoSub ListIt ' last name had no score
                 
                bName = True 
                sAcct = astr(1) 
                 
                 ' name starts with first non-number
                iStr = 3 
                Do While IsNumeric(astr(iStr)) 
                    iStr = iStr + 1 
                 Loop 
                 
                sName = "" 
                For iStr = iStr To iUB - 2 
                    sName = sName & " " & astr(iStr) 
                Next iStr 
                sName = Mid(sName, 2) 
                 
            ElseIf UCase(astr(iUB - 1)) = "SCORE:" Then 
                 'cell.Select
                vScore = astr(iUB) 
                bName = False 
                GoSub ListIt 
            End If 
        End If 
    Next cell 
    If bName Then GoSub ListIt 
    Exit Sub 
     
ListIt: 
    iRow = iRow + 1 
    rOut.Rows(iRow) = Array(sAcct, sName, vScore) 
    Return 
     
End Sub

Note the original post was: Parse Name & Number From Text String
http://www.ozgrid.com/forum/showthread.php?t=82808

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!

Re: Parse Name & Number From Text String

Ok, I have enclosed a small sample of what the input text looks like and what I would like the output to look like. The text is essentially exactly as I receive it except for some rows with personal information I deleted and the names have been changed. The spaces between the rows are the way it is received.

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.
Comments
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

thanks Domenic! Our office christmas party just started so I'll have to check it out next week. All the best!

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
...
...

Re: Display First Name Of User Name Only

Thank you, that works perfectly!

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")

Re: Add Page Break Based On Keyword

Thanks Dave!!! That's exactly what I needed. You just saved me a LOT of time reformatting pages

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!

Sub set_page_breaks()
Dim NumRows As Long
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
 
    ActiveWindow.View = xlPageBreakPreview
    ActiveWindow.Zoom = 85
 
NumRows = Range("A65536").End(xlUp).Row  'get the row  count
     
    ActiveSheet.ResetAllPageBreaks


With ActiveSheet
    FirstRow = 2
    LastRow = NumRows
    For iRow = FirstRow To LastRow
       'SET BREAK ON CHANGE IN COLUMN "D"
       If ActiveSheet.Cells(iRow, "D").Value _
       <> ActiveSheet.Cells(iRow + 1, "D").Value _
       And ActiveSheet.Cells(iRow + 1, "D").Value <> 0 Then
       'insert page break when rep# changes and the cell is not blank
       ActiveSheet.HPageBreaks.Add Before:=ActiveSheet.Cells(iRow + 1, "D")
       Else
      'do nothing
       End If
     
    Next iRow
    
End Sub

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"
         Range("A2").Select
         GoTo 1
    End If
    If IsEmpty(Sheets("Credentiales").Range("A1")) Then ufCredentiales.Show
    Open_Audit_File
1
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!