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
- How to open excel sheet in notepad++ - Guide
- Google sheet right to left - Guide
- Transfer data from one excel worksheet to another automatically - 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
