Function returns temporary sheet-CodePudding

is this possible to create a function that returns temporary sheet? Let's say I have got Sub as follow

Sub My_Sub()

  Dim harm As Worksheet
  Set harm = Sheets("my_sheet")
  
  Dim lastRow As Long, arr
    
  lastRow = harm.Range("A" & harm.Rows.Count).End(xlUp).Row 
  arr = harm.Range("T2:V" & lastRow).Value     
        
  MsgBox arr(2,5) 1
End Sub

Right now I'm working on harm = Sheets("my_sheet") and it loads whole sheet. Now I want to select part of that sheet and do the same operations so I wanted to write a function that will create temporary sheet, return it so in My_Sub I would have Set harm = ReturnSheet().

Is it possible? I want to load pseudo sheet from function, so I don't need to change anything in My_Sub (I mean those Ranges with column letter indexes).

Function ReturnSheet() As Worksheet

    Dim Rng As Range
    Dim lastRow As Long
    Dim lastCol As Long
    Set Rng = Selection
  
    lastRow = Selection.Rows.Count
    lastCol = Selection.Columns.Count
     
    ReturnSheet.Range("A2").Resize(lastRow, lastCol).Value = Rng
End Function

Right now I'm getting Object variable or with block variable not set at ReturnSheet.Range("A2").Resize(lastRow, lastCol).Value = Rng

CodePudding user response：

Try using the next Function. It returns a range meaning the selected cells without their first row:

Function ReturnRange() As Range
    Dim rng As Range: Set rng = Selection
    If rng.rows.count = 1 Then Exit Function 'for the case of selecting a range with only one row
    
    Set ReturnRange = rng.Offset(1).Resize(rng.rows.count - 1, rng.Columns.count)
End Function

You can test it using the next Sub:

Sub testReturnRange()
    Dim rng As Range
    Set rng = ReturnRange
    If Not rng Is Nothing Then Debug.Print rng.Address
End Sub