How to Select a Named Range on the Active Worksheet

To select the named range "StudentDetails" on the active worksheet, you can use either of the following examples:

Range("StudentDetails").Select

Application.Goto "StudentDetails"

How to Select a Range of Cells on a Worksheet in a Different Workbook

To select the range E5:J10 on a worksheet (Sheet5) in a different workbook (Book5.xlsx), you can use either of the following examples:

Application.Goto Workbooks("BOOK5.xlsx").Sheets("Sheet5").Range("E5:J10")

Application.Goto Workbooks("BOOK5.xlsx").Sheets("Sheet5").Range("E5", "J10")

Or, you can activate the worksheet, and then use method 4 above to select the range:

Workbooks("BOOK5.xlsx").Sheets("Sheet5").Activate
ActiveSheet.Range(Cells(5, 5), Cells(10, 10)).Select

How to Select a Range of Cells on Another Worksheet in the Same Workbook

To select the range E5:J10 on another worksheet (Sheet5) in the same workbook, you can use either of the following examples:

Application.Goto ActiveWorkbook.Sheets("Sheet5").Range("E5:J10")

Application.Goto ActiveWorkbook.Sheets("Sheet5").Range("E5", "J10")

Or, you can activate the worksheet, and then use method 4 above to select the range:

Sheets("Sheet5").Activate
ActiveSheet.Range(Cells(5, 5), Cells(10, 10)).Select

How to Select a Cell on Another Worksheet in the Same Workbook

To select cell F7 on another worksheet (Sheet3) in the same workbook, you can use either of the following examples:

Application.Goto ActiveWorkbook.Sheets("Sheet3").Cells(7, 6)

   -or-
 

Application.Goto (ActiveWorkbook.Sheets("Sheet3").Range("F7"))

You can also activate the worksheet, and then use method 1 above to select the cell:

Sheets("Sheet3").Activate
ActiveSheet.Cells(7, 6).Select

How to Select a Cell on a Worksheet in a Different Workbook

To select cell D4 on a worksheet (Sheet2) in a different workbook (Book2.xlsx), you can use either of the following examples:

Application.Goto Workbooks("Book2.xlsx").Sheets("Sheet2").Cells(4, 4)

    -or-

Application.Goto Workbooks("Book2.xlsx").Sheets("Sheet2").Range("D4")

You can also activate the worksheet, and then use method 1 above to select the cell:

Workbooks("Book2.xlsx").Sheets("Sheet2").Activate
ActiveSheet.Cells(4, 4).Select

How to Select a Range of Cells on the Active Worksheet

To select the range B2:D4 on the active worksheet, you can use any of the following examples:

ActiveSheet.Range(Cells(2, 2), Cells(4, 4)).Select

ActiveSheet.Range("B2:D4").Select

ActiveSheet.Range("B2", "D4").Select

How to Select a Cell on the Active Worksheet

To select cell C4 on the active worksheet, you can use either of the following examples:

ActiveSheet.Cells(4, 3).Select

-or-

ActiveSheet.Range("C4").Select

Selection of a specific sheet from a workbook

Hello Everyone,

The use case is as follows:
Data file which contains separate sheets for separate data. You need to select one sheet from all the available sheets in the file. 

In this example, 
1. User is asked to browse the data file
2. An userform shows a list of all the available sheets in the file
3. You select the required sheet and the data will be copied to your Tool workbook.

This may not be useful alone but you can combine this to your existing automation's where you are required to copy paste a data from a specific sheet in a file.

CODE in the module
===============
Sub fileOpen()
    toolWorkbook = ActiveWorkbook.Name
    On Error Resume Next
    data.Select
    data.Cells.Clear
    data.Range("A1").Select
    Dim strFileToOpen As String
    strFileToOpen = Application.GetOpenFilename(Title:="Please select an Excel file to open", FileFilter:="Excel Files *.xls* (*.xls*),")
    If strFileToOpen = "False" Then
        MsgBox "No file selected.", vbExclamation, "Status"
        Exit Sub
    Else
        Workbooks.Open Filename:=strFileToOpen
    End If
    dataWorkbook = ActiveWorkbook.Name
    frm_sheetSelect.Show
End Sub

Sub copyData()
    Dim tempName As String
    Workbooks(dataWorkbook).Activate
    ActiveSheet.Select
    
    tempName = ActiveSheet.Name
    
    Cells.Select
    Selection.Copy
    Workbooks(toolWorkbook).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    ActiveSheet.Name = tempName
    
    Workbooks(dataWorkbook).Activate
    ActiveWorkbook.Close False
    MsgBox "Data Import Complete", vbInformation, "Status"
End Sub

CODE in the userform

===================

Private Sub UserForm_Activate()

    Dim sh As Worksheet

    Workbooks(Module1.dataWorkbook).Activate

    For Each sh In ActiveWorkbook.Sheets

        frm_sheetSelect.lb_sheetNames.AddItem sh.Name

    Next sh

End Sub

Private Sub cmd_select_Click()

    If lb_sheetNames.Value <> "" Then

        Sheets(lb_sheetNames.Value).Activate

    Else

        MsgBox "Please select the sheet (containing the raw data) from the list to continue.", vbExclamation, "Status"

        Exit Sub

    End If

    Unload Me

    Call Module1.copyData

End Sub

Please put your thoughts and comments below so that I can improve.

THANKS EVERYONE.

SUBHAJIT.

LINK FOR THE EXAMPLE FILE

LINK FOR THE DATA FILE