Try this
Range("A10").Select
ActiveCell.Offset(1, 0).End(xlDown).Offset(1, 1).Select
Range(ActiveCell.End(xlToRight), ActiveCell.End(xlDown)).Select
On Apr 3, 12:00*pm, John Calder <JohnCal...@discussions.microsoft.com>
wrote:
> 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" wrote:
> > 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
>
> > On Apr 3, 4:55 am, John Calder <JohnCal...@discussions.microsoft.com>
> > wrote:
> > > Hi
>
> > > I run Win XP with Excel 2K
>
> > > I have a worksheet with a pivot table on it. I would like to copy thegrand
> > > totals of the pivot table into another worksheet.
>
> > > What i need is to have a macro that highlights the range in the pivottable
> > > 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 andDown
> > > 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- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
