Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table
I am hoping someone has a solution for this,
I have a pivot table that is looking at sales and regional info. I want to have the ability to write notes periodically when the table is in a filtered state. Once the notes are entered I want the sales reps to be able to hit a button to have the notes transfer over to the master list in a column labled "Notes". The note cells are outside the pivot table and the pivot table will change from user to user. It is too cumbersome to have the reps cycle throught the master list and insert notes for that customer. the master list is roughtly 54,000 entries long. I thought I could write a simple macro that found the name of the account (Column "C") in the pivot table and compared it to the master list (Column "F"), once a match was found it would tab over to the notes cell (Column "G") in the pivot table sheet, copy the cell, then go back to the same record in my master list, tab over to the notes cell (Column "AC") and paste the copied info. It sounds so easy and it probably is, but I just can't get the macro to work correctly. This is what I have so far Sub Add_Notes_To_Master() Range("C5:C500").Select ActiveWorkbook.Names.Add Name:="Sorted", RefersToR1C1:= _ "='Sales By State'!R5C3:R500C3" Range("g5:g500").Select ActiveWorkbook.Names.Add Name:="Notes", RefersToR1C1:= _ "='Sales By State'!R5C7:R500C7" Sheets("CrosstabSales2_US$_RegionSalesR").Select Range("F2:F696").Select ActiveWorkbook.Names.Add Name:="Active_Accounts", RefersToR1C1:= _ "='CrosstabSales2_US$_RegionSalesR'!R2C6:R696C 6" Set MyFirstRange = Range("Active_Accounts") Set MySecondRange = Range("Sorted") Set MythirdRange = Range("Notes") fnd = 0 For Each c In MyFirstRange For Each n In MySecondRange For Each P In MythirdRange If P.Value = "" and c.value = n.value Then ActiveCell.Offset(0, 4).Copy Paste.n.Offset(0, 23).Value fnd = 1 End If Next Next Next Range("B2").Select End Sub Does someone have a better way of doing this or am I asking for too much? Thanks in advance for any help. -- Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Filter lines with Pivot table and non pivot table columns | Charts and Charting in Excel | |||
Filter lines with Pivot table and non Pivot table columns | Excel Discussion (Misc queries) | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |