Copying Macro

[John Calder]

Hi

I run Win XP with Excel 2K

I have a worksheet with a pivot table on it. I would like to copy the grand
totals of the pivot table into another worksheet.

What i need is to have a macro that highlights the range in the pivot table
and copies it to the next worksheet. The copied data should go into the next
available blank row of the work sheet.

I have tried recording this macro and it works fine the first time I run it
but the next time I run it it copies over the last entry instead of going to
the next blank row. This is despite the fact that when I record the macro I
go to the column that I want the data to be copied too, press End and Down
which takes me too the last row and I then press the down arrow to take it to
the next blank cell.

This is the macro I recorded.

Sub CopyData1()

Range("B38:F38").Select
Selection.Copy
Sheets("DATA 2").Select
Range("C2").Select
Selection.End(xlDown).Select
Range("C47").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub


Obviously this probelm is caused by the Range("C47").Select in the code.

I would have thought that when I recorded the macro then when I selected the
arrow down at the end of the data that it would be reflected in the code.

I hope I have explaied this ok.

Can anyone help?


Thanks

John



























to the next sheet

 

[muddan madhu]

try this

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

 

[John Calder]

Hi

Thanks for your reply & help. It has almost fixed my problem.

At the moment I am copying the original data from a pivot table grand total
line.

What you gave me works great, however, I have just realized that if I
refresh the pivot table the grand total line may not be at the same row as
before, therfore the range I am copying may no longer be B38:F48.

The grand total line sums up the column, however in my case not every cell
may be inhabited in the pivot table but the 1st column of the pivot table
will be fully inhabited.

The 1st cell in the 1st column of the pivot table is A10.

So, what I think I need is that instead of the the code starting with:

Range("B38:F38").Select

I think it needs to have code that sends the curser to A10 then goes down
the column until it reaches the last cell (which is the one with the Grand
Total heading in it) then moves 1 cell to the right before selecting the
range to be copied which would be from B?: F?

I very much appreciate the help that has been give.

John

 

[muddan madhu]

Try this

Range("A10").Select
ActiveCell.Offset(1, 0).End(xlDown).Offset(1, 1).Select
Range(ActiveCell.End(xlToRight), ActiveCell.End(xlDown)).Select