Macro for Copying

[John Calder]

Hi

I run Excel 2K

I have a spreadsheet that has data on one worksheet and a Pivot Table in
another worksheet.

I would like to have a macro that copies the Grand Totals of the Pivot table
into another worksheet.

The row Grand Totals of the Pivot Table are on will change depending on a
date range that is entered.

The data that is copied from the pivot table must be copied to the next
available row in the spreadsheet that the data is copied to.

I have tried doing this by recording the macro but when I do the macro
records the actual range of the 1st copy and puts this range in its code.
Then if I try to run the macro again it always goes to the same range whis is
no good if the Pivot table has increased/decreased in size.

The starting cell for the Pivot Table is A10.

The range to be copied will be whatever row the Grand Totals are in and the
columns B,C,D,E & F


So, I guess waht I need is a macro that starts by sending the curser to cell
A10 then sending it down to that last row of the column, then moving across
one cell to the right, then using that cell (?B) and cells in columns C, D, E
& F as the range to be copied, then have the macro copy it to another
worksheet on the next available row. eg;(C20:F20) then the next time the
macro is run (C21:F21) etc


I hope I have explained this ok.


Cheers


John

 

[Paul]

John,

Something like this should do the trick.
You identify the pivottable, ascertain the main body range (TableRange1) and
count the rows(say n) and copy the last one(the nth).
The macro is assumed to be in the same workbook.

Sub CopyGrandTotalLine()
Dim pvt As PivotTable
Dim rng As Range
Dim rowCount As Long

Worksheets("Sheet3").Select
'Identify table and refresh with latest data.
'Assumed to be the first one, if there is more than one on the sheet.
Set pvt = Worksheets("Sheet2").PivotTables(1)
pvt.RefreshTable

'Identify main body range of table.
Set rng = pvt.TableRange1
'Find last row of this body and copy into next sheet.
With rng
rowCount = .Rows.Count
.Rows(rowCount).Copy Worksheets("Sheet3").Range("A2")
End With

End Sub

Regards

Paul

 

[John Calder]

Paul

Thank you for your prompt response. As you have no doubt worked ou I am not
a visual basic writer. I am sure what you have given me is what I neeed but I
have not been able to get it to work - no doubt somthing I am doing wrong.

I had some code that partially worked and I have included it this time (see
below)

Sub CopyData1()

Range("B38:F38").Select

Selection.Copy
Sheets("DATA 2").Select
rng = Cells(Rows.Count, "C").End(xlUp).Row + 1
Cells(rng, "C").Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False

End Sub

As you can see the problem with the above code is that it keeps going to the
Range("B38:F38").Select

All the code below this line appears to work. I am assuming that there is
part of your code that can be substituted for the - Range("B38:F38").Select -
in my code.



The worksheet that the data is being copied from is called PIVOT 1
The sheet it is being copied too is DATA 2
A9 is the 1st cell of the pivot table in PIVOT 1
A1 is where the data table starts on DATA 2


Example of data that is to be copied from the pivit table =

Col B?, Col C?, Col D?, Col E? & Col F?

Col A Col B Col C Col D Col E Col F
Grand Total 4.5 6.5 3.4 5.6 7.6




Thank you once again Paul, I really appreciate this


John

 

[John Calder]

Paul

I forgot to include how I interpreted your code to suit my spreadsheet,
which unfortunately i did not get to work (See below)

Sub CopyData1()

Dim pvt As PivotTable
Dim rng As Range
Dim rowCount As Long

Worksheets("PIVOT 1").Select
'Identify table and refresh with latest data.
'Assumed to be the first one, if there is more than one on the sheet.
Set pvt = Worksheets("PIVOT 1").PivotTables(1)
pvt.RefreshTable

'Identify main body range of table.
Set rng = pvt.TableRange1
'Find last row of this body and copy into next sheet.
With rng
rowCount = .Rows.Count
.Rows(rowCount).Copy Worksheets("DATA 2").Range("A2")
End With

End Sub