0

Need to transfer a template column over to a new sheet Sharepoint IDs. Currently that data is in column D, so I set up a configuration worksheet Configuration so a user can find the column number and my code can reference it. Whenever that data is in another column, the user can just input that and the below code will update while referencing.

Copying the data over, I used this:

Dim wsConfig As Worksheet
Set wsConfig = ThisWorkbook.Worksheets("Configuration")
wb.Worksheets("Roster").Range(Cells(wsConfig.Range("B4")), _
                              Cells(wsConfig.Range("B2")), _
                              Cells(lastrow, Cells(wsConfig.Range("B2")))).Copy , _
Destination:=wb.Workheets("Sharepoint IDs").Range(wb.Workheets("Sharepoint IDs").Cells(1, 1), _
wb.Workheets("Sharepoint IDs").Cells(1, 1))

in this case, instead of Cells(10,4) I'll put wsConfig.Range("B4") (10) and wsConfig.range("B2") (4) because that's referencing what the user inputs in those cells. This way it can adapt to whatever the user specifies.

I keep getting an object doesn't support this property or method error on that line, any ideas why?

5
  • 1
    And you've got a typo: Workheets. Commented Feb 12, 2020 at 16:58
  • 1
    Cells(wsConfig.Range("B4")) without a worksheet qualifier will always refer to the activesheet, so you should fix that first. Would help to show example values from the configuration sheet. Commented Feb 12, 2020 at 16:59
  • If you're using the .Value of a Range, would be good to be explicit about that. Commented Feb 12, 2020 at 17:04
  • Consider using an actual table / ListObject in your configuration sheet. That way you'll never need to work out what the last row is. Commented Feb 12, 2020 at 17:06
  • "instead of Cells(10,4)" -- where is this (10,4) coming from? Your post is unclear as it stands, it's impossible to come up with a working solution without making assumptions. Commented Feb 12, 2020 at 18:02

1 Answer 1

2

wsConfig is not needed. Give that sheet a code name e.g. ConfigSheet, and then use that identifier name in your code whenever you need to refer to that specific sheet. Note that you can only do this with worksheets that exist in ThisWorkbook at compile-time; see my CodeName: Sheet1 article for more information.

The One problem is the nesting of qualified and unqualified Worksheet member calls; the qualified member calls are made against the qualifying Worksheet object, but the nested, unqualified ones are referring to whatever the ActiveSheet happens to be at that time, and if that's not the Worksheet object that's qualifying the outer member call, then you can expect error 1004. Rubberduck's static code analysis tool flags these unqualified member calls as implicit ActiveSheet references.

wb.Worksheets("Roster").Range(Cells(wsConfig.Range("B4")), _
............................................................................
^ workbook qualifier          ^ Worksheet member call (unqualified)
   ^ Workbook member call           ^ worksheet qualifier
                        ^ Worksheet member call

The unqualified Cells member calls aren't necessarily going against the wb.Worksheets("Roster") sheet, but they clearly mean to. That should be made explicit.

A With block can solve this:

With wb.Worksheets("Roster")
    .Range(.Cells(ConfigSheet.Range("B4").Value, ConfigSheet.Range("B2".Value)) ...
End With

...but that's not ideal, because now everything is implicitly late-bound, because Worksheets returns an Object and now we lose compile-time validation.

Best declare a local variable for it... and now the compiler will start complaining about the Worksheet.Range call that has 3 arguments, instead of turning a blind eye (thanks to the implicit late binding) and only blowing up at run-time:

Dim rosterSheet As Worksheet
Set rosterSheet = wb.Worksheets("Roster")

Dim idSheet As Worksheet
Set idSheet = wb.Worksheets("Sharepoint IDs")

With rosterSheet
    .Range(.Cells(ConfigSheet.Range("B4").Value, _
           .Cells(ConfigSheet.Range("B2").Value), _
           .Cells(lastRow, .Cells(ConfigSheet.Range("B2").Value) _
    .Copy Destination:=idSheet.Cells(1, 1)
End With

Worksheet.Range takes 1 or 2 arguments: when given two arguments, it is expected that the first one is the upper-left cell, and the second one is the lower-right cell of the desired rectangular, contiguous range of cells.

I can't guess what specific range you're trying to copy, but this is what's going on. There isn't enough information in your post to come up with the actual working code; I'm assuming lastrow is whatever B4 says in the config sheet. If that's the case then this could be it:

Dim configColumn As Long
configColumn = ConfigSheet.Range("B2").Value
Debug.Assert configColumn > 0

Dim configRow As Long
configRow = ConfigSheet.Range("B4").Value
Debug.Assert configRow > 0

With rosterSheet
    .Range(.Cells(2, configColumn), .Cells(configRow, configColumn)) _
    .Copy Destination:=idSheet.Cells(1, 1)
End With

Avoid needlessly nesting Range and Cells calls, and always qualify them explicitly with a Worksheet object reference; also avoid continuating a line that isn't a full, complete, valid statement - kudos for not splitting the Destination:= named argument into multiple lines.

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

6 Comments

And here I wanted to close the question for the obvious Workheets typo that caused the original error... upvoted for a thorough discussion of the problem. That last code block looks so much better than the OP, and also makes what actually is going on much clearer.
@BigBen to be fair that typo would indeed have caused the error 438 OP is experiencing, assuming wb is either undeclared (missing Option Explicit!), or declared As Variant (implicitly or not), causing the ill-fated Workheets late-bound member call to fail at run-time. If wb is declared As Workbook, that error can't happen. Problem is, the other problems in the code are before that typo, so...
I agree it deserves a thorough answer! Though don't you get error 438 even if you declare wb as Workbook? Tested with Dim wb as Workbook, Set wb = ThisWorkbook, Debug.Print wb.Workheets.Count.
That would be a compile-time error, not runtime 438
Gives me RTE 438.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.