Search code examples
vbaexcelexcel-2013

Excel VBA To Set Variable To Worksheet

I am trying to set a Worksheet variable to the Activesheet but I am getting an error of

Object variable or with block variable not set

This is my syntax - what is the appropriate way of doing this?

Sub TestIt()
 Dim ws2 As Worksheet, ws1 As Worksheet

  ws2 = ActiveWorkbook.ActiveSheet
  ws1 = "Belgium"

  Debug.Print ws2
  Debug.Print ws1
End Sub
Solution

  • 1- You need to use the Set keyword to assign object variables.

    2- You cannot assign a sheet variable to a string directly, but you need to index through the Worksheets collection

    3- You cannot Debug.Print a worksheet, but only its name or some cell inside

    Sub TestIt()
    Dim ws2 As Worksheet, ws1 As Worksheet

    Set ws2 = ActiveWorkbook.ActiveSheet
    Set ws1 = ThisWorkbook.Worksheets("Belgium")

    Debug.Print ws2.Name
    Debug.Print ws1.Cells(1,1).Value
End Sub