Pivot Table Macro Needed

[timbo1957]

Hi

I have a couple of pieces of code from other macros and I want to combine them to make one piece of code if possible.

I want to create a pivot table in a new worksheet using the data in the Sheet Report1.

The data starts in A2 and goes across to Q2 and the end of the data varies.

I have this which will count the rows in column A.

Please Login or Register  to view this content.

I have then amended that to take off the header and sum columns.

Please Login or Register  to view this content.

Then I have this to select the range.

Please Login or Register  to view this content.

I want to put this in here but don't know how to.

Please Login or Register  to view this content.

Basically getting rid of the hard coding of the range in the pivot table part.

Any help much appreciated.

[romperstomper]

Do you mean your headers are in row 2 on the data sheet? Why are you subtracting 2 and not 1 from the last row variable?

[timbo1957]

My headers are in row 1 and the total for the sheet is in the very last row which is variable. So I thought I needed to take off 2.

[romperstomper]

Nope - you need to include headers for the pivot table to use as field names. So:

Please Login or Register  to view this content.

Note I changed the C18 to C17 since you said column Q, not R.

[timbo1957]

Hi romperstomper,

This works almost perfectly. How can i make the pivot table start in A1 of the new sheet? It is spread across C:G.

Regards

Tim

[romperstomper]

You need to change this bit:

Please Login or Register  to view this content.

You can switch it to:

Please Login or Register  to view this content.

[timbo1957]

Brilliant, many thanks.