CODEWITHSUNDEEP
vbaexcel
Nishant
Nishant

Reputation: 404

How to select some selected Pivot table area?

I am trying to select and copy some selected area of a pivot table. I am able to determine the amount of area I want and am able to display the range in a message box which is not my objective. I want to copy that selected range. My code looks like this. I want to copy the Values in the range (toprow1,leftcoloumn:lastrow,rightcoloumn). FYI the message box code is something I don't need Its just their to tell you the Range no. 

    Sub PivotTableRangeAreas()

    With ActiveSheet.PivotTables(1)

  Dim TopRow1 As Long, TopRow2 As Long, LastRow As Long
  Dim LeftColumn As Long, RightColumn As Long

  TopRow2 = .TableRange2.Row
  With .TableRange1
  TopRow1 = .Row
  LastRow = .Rows.Count + .Row - 1
  LeftColumn = .Column
  RightColumn = .Columns.Count + .Column - 1
  End With

  MsgBox "The pivot table named " & .Name & vbCrLf & _
  "occupies these range elements:" & vbCrLf & vbCrLf & _
  "With the Report (Page) field: " & vbCrLf & _
  .TableRange2.Address(0, 0) & vbCrLf & _
  "Without the Report (Page) field: " & vbCrLf & _
  .TableRange1.Address(0, 0) & vbCrLf & vbCrLf & _
  "First row, with the Report (Page) field: " & TopRow2 & vbCrLf & _
  "First row, without the Report (Page) field: " & TopRow1 & vbCrLf & _
  "Last row: " & LastRow & vbCrLf & _
  "Left column: " & LeftColumn & vbCrLf & _
  "Right column: " & RightColumn, , "Pivot table location."

  End With

  End Sub

Upvotes: 0

Views: 1225

Answers (1)

MattCrum
MattCrum

Reputation: 1110

I'm guessing it's just the values that you want to copy? If so, try starting with something like this - it'll put the values into Sheet2 starting at range A1. I'm not sure which range from the pivot table you want to copy to where - you'll have to change some of this to suit:

Sub CopyRange()

Dim vArray() As Variant

'Copies the values between (Toprow1, LeftColumn) and (LastRow, RightColumn) into an array
vArray = ActiveSheet.Range(Cells(TopRow1, LeftColumn), Cells(LastRow, RightColumn)).Value
'Pastes the values from the array into Sheet2, starting at A1
Sheet2.Range("A1").Resize(UBound(vArray, 1), UBound(vArray, 2)).Value = vArray

End Sub

Upvotes: 1

Related Questions