Macro to copy data from pivot table [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I have created a macro that creates a pivot table from set of raw data and performs some calculation on that data. Then I have to filter the data based on the calculation and I want to copy the filtered data from pivot table to another sheet. The no of rows are different every day and the macro must copy the entire data. Can someone plz help.





1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
764
First copy the pivot table and paste as values to a new sheet. use macro recorder to record your actions. then you need to customize the recorded macro. since you say that row would change , you need to find out what is the new row count is.

you can use these two functions for finding out number of rows and columns on sheet that are in use

Function getLastRow(sSheetName As String) As Long
' get last use row on the sheet

   Dim Cell          As Range
   
      Set Cell = Sheets(sSheetName).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)

   If Cell Is Nothing Then
      getLastRow = 0
   Else
      getLastRow = Cell.Row
   End If
   Set Cell = Nothing

End Function

Function getLastColumn(sSheetName As String) As Long
' get last use row on the sheet

   Dim Cell      As Range
   
      Set Cell = Sheets(sSheetName).Cells.Find("*", Cells(1, 1), , , xlByColumns, xlPrevious)

   If Cell Is Nothing Then
      getLastColumn = 0
   Else
      getLastColumn = Cell.Column
   End If
   Set Cell = Nothing

End Function

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!