Hello, I am hoping someone kind could assist me. I have a sales representative report that is automatically updated every few minutes. This shows the Customer Name, Last Contact Date, and then Monthly Spend (current Month). From this data I need to monitor which of my customers are the Top 20 in terms of monthly spend. However I need to be alerted by email (I have CDO code borrowed from elsewhere to send email) if those 20 customers change, i.e. one customer falls behind and another takes it's place. I will try and upload a sample report shortly which hopefully will help to explain, but in the meantime, if anyone has any bright ideas, I would be grateful. Many thanks
Macro to copy and paste table if specific data in the table changes
-
-
-
Re: Macro to copy and paste table if specific data in the table changes
I have attached a sample report. Cells F28:F64 is the range that I need to monitor. So I need a report to come from here to show the Top 20 in this range, sorted by value, together with the Customer Name. I would appreciate any thoughts on this. I should also explain that this Pivot Table is automatically created by our software during the export, and so I am limited as to what I can do with it directly. ***I AM HAVING DIFFICULTIES UPLOADING ANYTHING AT THE MOMENT - IS THERE A FILE SIZE LIMIT OR SOMETHING??***
-
Re: Macro to copy and paste table if specific data in the table changes
There's a limit.
Either try zipping the file or upload to Dropbox or similar and post a link to it here.
-
Re: Macro to copy and paste table if specific data in the table changes
[post removed]
-
Re: Macro to copy and paste table if specific data in the table changes
No thoughts on your problem, but are you aware the original Customer Names are visible in the Filter drop-down...?
-
-
Re: Macro to copy and paste table if specific data in the table changes
no, i wasn't - thanks for alerting me!
-
Re: Macro to copy and paste table if specific data in the table changes
... and the hidden sheets have all original details (including email address).
-
Re: Macro to copy and paste table if specific data in the table changes
Winging it here - but in Sheet1, Cell AW28, perhaps something like:
[f]
=LARGE(AS$28:AS$64, ROW()-27)
[/f]This returns the Highest value found in AS28:AS64. The Row()-27 (as it is row 28 will evaluate to 1, so it finds the highest value. AS you copy down, Row() - 27 changes to 2, 3, 4 etc. This will give you an ordered list of the highest x values; x depending on the number of times you copy down.
In AX28, add:
[f]
=INDEX(C$28:C$64,MATCH(AW28,AW$28:AW$47,0))
[/f]
and copy down x rows. That returns the Customer name for each valueThe two formulas can be combined as
[f]
=INDEX(C$28:C$64,MATCH(LARGE(AS$28:AS$64, ROW()-27),AW$28:AW$47,0))
[/f]*** NOTE: Formula edited - previously referred to AW29:AW48 ***
In the Sheet1 Code module, add the following
CodePrivate Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable) With Sheet1 .Range("AX28:AX47").Copy .Range("AY28").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End With End Sub
This copies the ranked list in AX27:AX47 and uses paste special values to paste the values only in AY28. You can then compare The before and after rankings for each customer in the top 20... I haven't included that, but it should be easy enough to work out.
There are other ways to do this, but I have broken it down into discreet steps for simplicity.
-
Re: Macro to copy and paste table if specific data in the table changes
pls assist to convert flat table in 2 dim. cross table.
-
Re: Macro to copy and paste table if specific data in the table changes
n.gupta
The policy on this board is you do not ask questions in threads started by other members, no matter how similar your query is.
If you think this, or any other thread can help clarify things then you can include a link by copying the URL from thr address bar of your browser and pasting into your thread.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!