CODEWITHSUNDEEP
excelvba
Mturks83
Mturks83

Reputation: 89

Declare Worksheet as Variable

I'm trying to declare a particular worksheet as a variable.

I'm receiving a runtime error 9

Sub Test()
    Dim ws As Worksheet
    Set ws = Workbooks("C:\Users\mturkenburg\OneDrive - WHISPIR\Whispir\Desktop\IT Learnings\VBA\The Complete VBA Excel VBA Course for Beginners.xlsm").Worksheets("Testsheet")
End Sub

Upvotes: 0

Views: 98

Answers (1)

MRah
MRah

Reputation: 950

You need to open the workbook. Here is an example, I also declared a workbook variable so that it's easier to do workbook operations once you are finished doing work with the worksheet.

Sub Test()
    Dim wb As Workbook
    Dim ws As Worksheet
      
    ' The workbook file path string should be another workbook that 
    ' you're trying to open (Not the current workbook)
        
    Set wb = Workbooks.Open("C:\Users\mturkenburg\OneDrive -    WHISPIR\Whispir\Desktop\IT Learnings\VBA\The Complete VBA Excel VBA Course for Beginners.xlsm")
     
    '   Use the following if you're trying to access the worksheet
    '   Inside the Workbook
    '   Set ws = ThisWorkbook.Worksheets("TestSheet")
        Set ws = wb.Worksheets("Testsheet")
         
    ' Do your thing with worksheet
    Debug.Print ws.Name
        
    ' Close the workbook
    wb.Close False
        
End Sub

Here is another example, if you have multiple workbooks open and you want to access one of them,

Sub Test()
    Dim ws As Worksheet

    '   Only use the name of the workbook inside the Workbooks Function
    Set ws = Workbooks("The Complete VBA Excel VBA Course for Beginners").Worksheets("TestSheet")
        
    '   Do your thing with the worksheet
    Debug.Print ws.Name
End Sub

Upvotes: 1

Related Questions