Referring to Worksheets("ABC") gives an error? RRS feed

  • Question

  • Line 6 works. How come I am getting the following error on line 7? "Run-time error '438': Object doesn't support this property or method." This also happens if I use "Sheet1" instead of "ABC." (I wonder why I don't get any pop-up choices when I type a period after 'WorkSheets("ABC")' for line 6 and 7.)

    1 Option Explicit
    3 Sub ButtonCopy_Click()
    4     Dim FromWS, ToWS As Worksheet
    5     Dim test As String
    6     test = Worksheets("ABC").Range("A1").Value
    7     FromWS = Worksheets("ABC")

    • Edited by GregJ7 Monday, June 1, 2015 1:10 AM
    Monday, June 1, 2015 1:09 AM


  • Re: using a worksheet object in Excel

    Dim FromWS, ToWS As Worksheet
      Should be...
    Dim FromWS as Worksheet, ToWS As Worksheet

    In your code "FromWs" is a Variant, which will work but is not as efficient.

    Also you have to create an new instance of an object (worksheet) by using the Set statement...
      Set FromWS = Worksheets("ABC")

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)
    • Marked as answer by GregJ7 Monday, June 1, 2015 4:29 AM
    • Edited by James Cone Saturday, October 22, 2016 5:21 PM
    Monday, June 1, 2015 4:24 AM