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 — Error 1004
– Error 1004, Commented Jun 12, 2019 at 13:20

Tim Stack · Accepted Answer · 2019-06-12 12:51:56Z

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, 2019 at 12:51

Tim Stack

3,2483 gold badges25 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Hakon Over a year ago

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!

Tim Stack Over a year ago

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

Error 1004 · Accepted Answer · 2019-06-12 13:22:09Z

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, 2019 at 13:22

answered Jun 12, 2019 at 13:06

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

8 Comments

Hakon Over a year ago

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.

Error 1004 Over a year ago

i you want grab data located in the same cells in different sheets you have to loop sheets and grab data.

Hakon Over a year ago

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?

Error 1004 Over a year ago

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?

Hakon Over a year ago

Thank you, I am using your examples to fix my code as we speak.

|

Collectives™ on Stack Overflow

Grabbing Data From Different Sheets Using .Select

2 Answers 2

2 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related