VBA Help : Small Automation using VBA Macros

[sachinns]

Hi Friends,


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]key[/TD]
[TD]Qty[/TD]
[TD]Related Products[/TD]
[TD]Related Cost[/TD]
[TD]Invoiced at[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]-1[/TD]
[TD]CAR[/TD]
[TD]100$[/TD]
[TD]Jan[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]-1[/TD]
[TD]CAR[/TD]
[TD]150$[/TD]
[TD]Jan[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]0[/TD]
[TD]CAR[/TD]
[TD]170$[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1[/TD]
[TD]Bike[/TD]
[TD]50$[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1[/TD]
[TD]Bike[/TD]
[TD]40$[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]1[/TD]
[TD]Cycle[/TD]
[TD]20$[/TD]
[TD]Mar[/TD]
[/TR]
</tbody>[/TABLE]

This is my sheet and i want to automate 3 tasks using VBA . Can someone please help me to do this .

1. Generate a summary page which displays sum of count against each product on column Related Product and Sum of Related Cost .


2. Split complete data to different work sheet on basis of column Invoiced At .


3. Highlight rows which has column QTY value less than 0.

 

[Jon Peltier]

The three tasks are easy to do manually.

First, select the data, and press Ctrl+T to turn the range into a Table. This will make life easier.

Then, with your active cell in this table, go to the Insert tab of the ribbon, Pivot Table. Choose a cell somewhere. Using the Pivot Table task pane, drag Related Products to the Rows area, then go up and drag it again to the Values area, where Excel automatically changes it to Count of Related Products. Then drag Related Cost to the Values area, where Excel changes it to Sum of Related Costs.

Then select all of the rows of the table, and go to the Home tab of the ribbon, and choose Conditional Formatting. Select New Rule, then Use a Formula. Assuming the active cell is in Row 2, you want this formula:

=$B2<0

and assign some highlight formatting.

If you really want VBA for this, you could record and fix up a macro (or post back).