Append column for pasting
 

I want to paste the results from a pivot table into a column. I want t
do this each time the pivot table refreshes so that it is pasting int
the first empy row of the column range (i.e. its appending to th
previous results.

I can think of two ways to do this but don't really know how to do it'

1) name first cell in paste column and

Range("A").Select
Range(Selection, Selection.End(xlDown)).Select
And paste destination = 1 extra row to this

2) Use dynamic past range and paste to it plus 1 extra row.

Any thoughts on how to code this?

Thank

--
Message posted from http://www.ExcelForum.com

Append column for pasting
 

One way. This will need to be adapted as required :-

'----------------------------------
Sub test()
Dim FromSheet As Worksheet
Dim ToSheet As Worksheet
Dim C1 As String ' top of copy range
Dim C2 As String ' bottom of copy range
Dim LastRow As Long
'- from
Set FromSheet = ActiveSheet
C1 = "B3" ' top of pt column
C2 = "B" & FromSheet.Range(C1).End(xlDown).Row
'-to
FromSheet.Range(C1 & ":" & C2).Copy
Set ToSheet = ThisWorkbook.Worksheets("Sheet1")
LastRow = ToSheet.Range("A6536").End(xlUp).Row + 1
ToSheet.Paste Destination:=ToSheet.Range("A" & LastRow)
End Sub
'--------------------------------


---
Message posted from http://www.ExcelForum.com/