Public myActiveSheet As Worksheet ' das ActiveSheet wird hier sichergestellt
Sub Main()
myActiveSheet = createTestSheet(ActiveSheet.Name) ' erstellt eine Arbeitskopie
UsedRangeRows = myActiveSheet.UsedRange.Rows.Count ' verwendet die Arbeitskopie
...
End Sub
Public Function createTestSheet(Optional myTestSheet As String) As Worksheet
If sheetExists("myTestSheet") Then
Application.DisplayAlerts = False
Sheets("myTestSheet").Delete
Application.DisplayAlerts = True
End If
If Not myTestSheet = Empty Then
Sheets(myTestSheet).Activate
End If
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = "myTestSheet"
Set createTestSheet = ActiveSheet
End Function