CODEWITHSUNDEEP
excelvba
Hakon
Hakon

Reputation: 99

Grabbing Data From Different Sheets Using .Select

I must jump between different excel sheets to grab data for my VBA to output what I want. I've been using the Sheets("name").Select function to jump between the sheets. Sometimes it works and lets me run the program, but other times I get a runtime error. I want this to work 100% of the time every time, and always get disheartened whenever it fails due to the select function. If anyone has any tips or recommendations I would love for you to share them! Any help would be greatly appreciated.

Sheets("Test").Select

Run-time Error '1004': Select Method of Worksheet Class Failed

Upvotes: 0

Views: 64

Answers (2)

Error 1004
Error 1004

Reputation: 8220

With Statement:

Option Explicit

Sub test()

    'Create a witrh statement with the Sheet name you want
    With ThisWorkbook.Worksheets("Sheet1")
        'Refer to cell A1 (don t forget the "." before Range)
        .Range("A1").Value = ""
        'Refer to cell A1 (don t forget the "." before Cells)
        .Cells(1, 1).Value = ""
    End With

End Sub

Loop Worksheets:

Option Explicit

Sub test()

    Dim ws As Worksheet

    'Loop Sheets
    For Each ws In ThisWorkbook.Worksheets

        With ws
            'If sheet name is Sheet1 or Sheet3
            If .Name = "Sheet1" Or .Name = "Sheet3" Then

                'Refer to cell A1 (don t forget the "." before Range)
                .Range("A1").Value = 2
                'Refer to cell A1 (don t forget the "." before Cells)
                .Cells(1, 1).Value = 10
            ElseIf .Name = "Sheet2" Then
                'Refer to cell A1 (don t forget the "." before Range)
                .Range("A1").Value = 5
                'Refer to cell A1 (don t forget the "." before Cells)
                .Cells(1, 1).Value = 20

            End If

        End With

    Next ws

End Sub

Set Worksheet To Variable

Option Explicit

Sub test()

    Dim ws1 As Worksheet, ws2 As Worksheet

    With ThisWorkbook
        Set ws1 = .Worksheets("Sheet1")
        Set ws2 = .Worksheets("Sheet2")
    End With

End Sub

Upvotes: 0

Tim Stack
Tim Stack

Reputation: 3248

Don't use Select (or Activate for that matter), it's bad practise and leads to errors rather quickly.

This thread is a great help as to why and how you should avoid using it.

There is no need to select a sheet before getting data from it. For example:

Sub test()
'''
'Bad practise:
'''
Sheets("Sheet1").Select
i = Range("A1")

'''
'Good practise
'''
i = Workbooks("Book1").Sheets("Sheet1").Range("A1").Value

'''
'Better practise
'''
Dim wb As Workbook
Dim sht As Worksheet
Dim i As String

Set wb = Workbooks("Book1")
Set sht = wb.Sheets("Sheet1")

With sht
    i = .Range("A1").Value
End With

End Sub

Upvotes: 1

Related Questions