Macro to copy data from pivot table
Closed
Excel Learner
-
Feb 26, 2011 at 01:50 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 27, 2011 at 05:30 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Feb 27, 2011 at 05:30 PM
Related:
- Excel vba copy pivot table data to new sheet
- Mark sheet in excel - Guide
- Number to words in excel formula without vba - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to screenshot excel sheet - Guide
- How to open excel sheet in notepad++ - Guide
1 response
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 27, 2011 at 05:30 PM
Feb 27, 2011 at 05:30 PM
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
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