Grabbing Data From Different Sheets Using .Select

Question

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

Possible duplicate of [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Error 1004, Jun 12 '19 at 13:20

score 1 · Answer 1 · answered Jun 12 '19 at 12:51

1

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

answered Jun 12 '19 at 12:51

Tim Stack

3,209
3
18
39

Ah, I did not realize that select and activate were so disliked in this community. I guess I will end up having to rewrite some stuff. Thank you! – Hakon Jun 12 '19 at 13:09
Not so much disliked by this community, it's just *very* prone to errors, slows down your code and is generally unnecessary. You're much better off without it! If my answer helped you, please don't forget to accept it by clicking on the check icon under the downvote buttton – Tim Stack Jun 12 '19 at 13:14

Error 1004 · Accepted Answer · 2019-06-12T13:22:09.433

0

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

edited Jun 12 '19 at 13:22

answered Jun 12 '19 at 13:06

Error 1004

7,877
3
23
46

If I did this, would I still be able to call data the same way, or would I have to change how I grabbed the data? Ex: Cells(3,2).value for the value in that cell on that sheet. – Hakon Jun 12 '19 at 13:08
i you want grab data located in the same cells in different sheets you have to loop sheets and grab data. – Error 1004 Jun 12 '19 at 13:09
Their difference cells in different sheets. Could I just do With ThisWorkbook.Worksheets("Sheet1") .Range("A1").value End With With Thisworkbook.Worksheets("Sheet2") .Range("A2").value EndWith to get the data from two different cells in 2 different sheets? – Hakon Jun 12 '19 at 13:13
You question is totally unclear. There are a lot of ways to avoid `.Select`. Many people try to help you and you down vote all answer? – Error 1004 Jun 12 '19 at 13:19
I was not the one who downvoted your answer, I am the upvote that make it zero. Someone else was the one who downvoted you. Why would I downvote you then ask you questions? – Hakon Jun 12 '19 at 13:20
many answer delete due to down vote in this question. i have upload another answer. you could see it – Error 1004 Jun 12 '19 at 13:22
@Hakon i have upload another solution – Error 1004 Jun 12 '19 at 13:27
1

Thank you, I am using your examples to fix my code as we speak. – Hakon Jun 12 '19 at 13:34

Grabbing Data From Different Sheets Using .Select

2 Answers2