Pasting data in the reverse order

You might have come across with situation where you wanted to paste your data in reverse order. In the example below we want the numbers in Data column in reverse order. This can be done using excel formula.

Data	Reverse Data
1	5
2	4
3	3
4	2
5	1

Following formula can be used:

=INDEX($A$2:$A$6,ROWS(A2:A$6))

Copy data from Excel to Notepad

VBA Code to copy data from Excel to Notepad:


Sub copyPastedata_in_Notepad()
With Application
Selection.Copy
Shell "notepad.exe", 3
SendKeys "^v"
VBA.AppActivate .Caption
.CutCopyMode = False
End With

End Sub

VBA Code to Collapse and Expand columns

VBA Code to Collapse and Expand columns based on the given criteria:


Sub Collapse_Expand()
Dim w As Worksheet
Dim pt As PivotTable
Dim FinYear As String
Dim QTD As String

FinYear = "FY" & Right(Date, 2)
QTD = "Q3                             "
        
        For Each w In ThisWorkbook.Worksheets
            If ActiveSheet.Name = "Sheet1" Or ActiveSheet.Name = "Sheet2" Then
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Rev_Cost").ShowDetail = False
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter").ShowDetail = False
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").ShowDetail = False
                
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Rev_Cost").PivotItems("Rev").ShowDetail = True
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Rev_Cost").PivotItems("Cost").ShowDetail = True
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Year").PivotItems(FinYear).ShowDetail = True
                ActiveSheet.PivotTables("PivotTable1").PivotFields("Quarter").PivotItems(QTD).ShowDetail = True
            End If
                On Error Resume Next
                Sheets(ActiveSheet.Index + 1).Activate
                If Err.Number <> 0 Then Sheets(1).Activate
        Next
End Sub