How To Store Current Range Selection using VBA in Excel?

Q. How To Store Current Selection using VBA in Excel?

A. Use the foloowing VBA script:

Option Explicit
Private Sub Example()
    Dim ActSheet As Worksheet
    Dim SelRange As Range
 
    Set ActSheet = ActiveSheet
    Set SelRange = Selection
 
    '' Any code here
    'Dim NewSheet As Worksheet
    '
    'ActiveSheet.Range("A1").Select
    '
    'Set NewSheet = ThisWorkbook.Sheets().Add()
    'NewSheet.Move After:=Sheets(ThisWorkbook.Sheets().Count)

    ActSheet.Select
    SelRange.Select
End Sub

Let's discuss how it works. First, force explicit declaration of all variables:

Option Explicit

To store selection we need two variables (Worksheet and Range):

Dim ActSheet As Worksheet
Dim SelRange As Range

Then we store active Worksheet and current range selection:

Set ActSheet = ActiveSheet
Set SelRange = Selection

Now we can use any VBA code (add new sheets, select or hide cells etc) and then restore origin selection:

ActSheet.Select
SelRange.Select