VBA Code to copy specific columns in a pivot table & paste to another worksheet

Question

I'm updating an excel spreadsheet which has VBA codeing in the background. The changes I have made to a table means the vba code now doesn't work correctly. The original code is:

Public Sub FilterPivot()
Dim name As String
name = ThisWorkbook.Worksheets("Report2").Range("Reportname").Text

Dim pv As Worksheet
Set pv = ThisWorkbook.Worksheets("Pivot")
Sheets("Pivot").Activate

       
         pv.PivotTables("PivotTable1").PivotFields( _
        "[AB].[Reporting Name].[Reporting Name]").AutoSort xlDescending, _
        "[Measures].[Sum of MG/PCU]"
     
    Sheets("Report2").Activate
    Range("I:I").EntireColumn.Hidden = False
    Range("I97:I125").Select
    Selection.ClearContents
    
    Sheets("Report2").Activate
    Range("B97:E125").Select
    Selection.ClearContents
    
    pv.PivotTables("PivotTable1").RowRange.Copy
Sheets("Report2").Activate
Range("$B$97").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
    pv.PivotFields("Sum of Usage").DataBodyRange.Copy
Sheets("Report2").Activate
Range("$C$97").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    pv.PivotTables("PivotTable1").DataBodyRange.Copy
Sheets("Report2").Activate
Range("$C$97").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        pv.PivotTables("PivotTable1").DataBodyRange.Copy
Sheets("Report2").Activate
Range("$H$63").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Application.CutCopyMode = False

End Sub

I need the Reporting name (Row value in the pivot) to be copied & pasted first. The existing vba code seems to work for this where rowrange.copy has been used. Pivot Table

I need to copy the first values column 'Sum of Usage' to another worksheet within the workbook in a specific location & complete the same process for 2 more value columns within the pivot table ('Sum of MG/PCU' & 'Sum of DCD'). The columns where they are to be pasted to aren't next to each other so the Bodydatarange.copy method doesn't work the way I need it to.

I need the reporting name to be copied into column B row 97 onwards. 'Sum of Usage' needs to be copied to column C Column D will be formula driven. 'Sum of MG/PCU'needs to be copied to cpolumn E 'Sum of DCD' needs to be copied to column I after it has been unhidden. The columns will always remain the same (unless there are any further additions) but the rows may be more or less than in the pivot screenshot Report Table

Currently the code copies the reporting name but I would prefer the header not to be there (I can hide the line though if needed so not a big problem) & then copies all of the pivot table data & pastes into the report table one column after the other. The inbetween columns will have formulas stored within them so I need to be able to copy & paste each pivot column one at a time.

There is a power pivot table running in the background but I don't know how this works. The spreadsheet & vba code have all been set up by someone who has since left the business.

This particular code is then incorporated into another code block which autofilters the pivot table based on the name selected within a drop down list & will update the data within the report & then create a pdf & email.

Any help is appreciated

What did you already try to fix this, and what specific problem(s) did you run into? It's kind of boring for folks here just to carry out your "please fix as described" instructions, rather than helping you complete the task.... — Tim Williams
– Tim Williams, Commented Jun 20 at 18:40

Jason Ronne · Accepted Answer · 2025-06-25 11:50:02Z

0

you can copy with a .Copy and paste as it is

if all columns are the same length save the row numbers as variables and just dynamically copy them across

'Starting point (the first row containing data to copy)
rowStart = 4

'End Point (get the last row with data)
rowEnd = Sheet1.Range("A4").End(xlDown).Row

'then copy the data (change the "A"s to select the column you wish
Sheet1.Range("A" & rowStart & ":A" & rowEnd).Copy

'paste where you need it ("A4" is the first cell where the pasted data will be)
Sheet3.Range("A4").PasteSpecial Paste:=xlPasteValues

answered Jun 25 at 11:50

Jason Ronne

661 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Code to copy specific columns in a pivot table & paste to another worksheet

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related