Data Selection 2

[NTB]

I posted this following my previous Solved thread, but I thought it would be too confusing so I create a new one.
http://www.excelforum.com/excel-prog...ipulation.html

The codes from 2 responses above have helped me tremedously - But now I got stuck again. Please help me w/ the questions below.

1) Sorry that I do not attach a sample - the code executes the way I want, but I do believe there's a shorter and better way.

Basically, I would like to copy row D, E, F, I & J from sheet Consolidation and paste it in Pivot tab from A2

It would be a lot easier if I am to paste from A1 in Pivot as I could just do
Range("D:D,E:E,F:F,I:I,J:J").Copy from Consolidation but I need from A2

Please Login or Register  to view this content.

2) My second portion is in the attachment.

Once again, thank you for your help!

NTB

[NTB]

I hope this post hasn't been forgotten

[mdbct]

If all columns end at the same row you can use:

Please Login or Register  to view this content.

From the questions in the workbook:

Is there a formula to set the Range for Pivot table to always set A : E where E's value > 0

If your E column is sorted Descending, you can create a named range using the following formula and then assign that named range as the data source for your pivot table.

Please Login or Register  to view this content.

3) How do I apply the formula in K15 down the line where J's value existed using VBA

Please Login or Register  to view this content.

[NTB]

Thank you much for your help!
I will test the code soon as I got stuck w/ another project at work