Hello,
I have created a macro that pulls numerous pivots into a new tab - in the new page the values seem to be coming across as paste 123 ( no formatting). I want to add some colors so i was going to color the pivots and let the macro pull across the data and formatting. To do this i thought the paste value transpose was best to do this but i haven't been able to get this working.
Here is the Code
Sub Button2_Click()
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "pivot test"
Dim Last_Row As Long
Last_Row = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Ordered Pivot").Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel, True
Selection.Copy
ws.Range("A1").PasteSpecial xlPasteValues
I also added this code in to fit the data correctly into columns
Application.ScreenUpdating = False
Dim wkSt As String
Dim wkBk As Worksheet
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
Cells.EntireColumn.AutoFit
Next wkBk
Sheets(wkSt).Select
Application.ScreenUpdating = True
Any thoughts?
I have created a macro that pulls numerous pivots into a new tab - in the new page the values seem to be coming across as paste 123 ( no formatting). I want to add some colors so i was going to color the pivots and let the macro pull across the data and formatting. To do this i thought the paste value transpose was best to do this but i haven't been able to get this working.
Here is the Code
Sub Button2_Click()
Dim ws As Worksheet
Set ws = Worksheets.Add
ws.Name = "pivot test"
Dim Last_Row As Long
Last_Row = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Ordered Pivot").Select
ActiveSheet.PivotTables("PivotTable2").PivotSelect "", xlDataAndLabel, True
Selection.Copy
ws.Range("A1").PasteSpecial xlPasteValues
I also added this code in to fit the data correctly into columns
Application.ScreenUpdating = False
Dim wkSt As String
Dim wkBk As Worksheet
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
Cells.EntireColumn.AutoFit
Next wkBk
Sheets(wkSt).Select
Application.ScreenUpdating = True
Any thoughts?