0

I am trying to copy/paste from one worksheet within a workbook to another worksheet within the same workbook. I am having a very frustrating problem with the following error while trying to assign my ranges:

Run-time error '438': Object doesn't support this property or method.

Dim c As Range
Dim ws1 As Worksheet
Dim ws2 As Range
Dim tgt As Range

Set ws1 = Sheets("Sheet 1")

Set c = ws1.Range("Named_Range").Cells(1, 1)
Set tgt = ws1.Range(c, c.Cells(10, 1)) 'Purpose is to create a range with the first 10 cells of Named_Range, but I need it to be dynamic

'To test that range is being assigned correctly
tgt.Select

At this point I get the error code referenced above. However, the code runs provided that I add 

ws1.Select

above "Set c = ..."

Any idea what's going on? My understanding is that using 'Select' in this way is not recommended, and I don't want to be messing around with selecting particular Sheets anyway because I am trying to eventually toggle between them while copy/pasting.

bhbennett3
  • 123
  • 8

1 Answers1

1
  1. Worksheet is different than Range, ws1 should be a defined as a Worksheet.
  2. The named range cannot have spaces in its name. This works:
Sub TestMe()

    Dim c As Range
    Dim ws1 As Worksheet
    Dim ws2 As Range
    Dim tgt As Range

    Set ws1 = Sheets("Sheet 1")
    Set c = ws1.Range("Named_Range_A").Cells(1, 1)
    Set tgt = ws1.Range(c, c.Cells(10, 1))

    Debug.Print tgt.Address

End Sub

As additional bonus - Worksheet is a different object than Sheet, you may consider using Set ws1 = Worksheets("Sheet 1"). The Sheet object be either Worksheet or Chart, thus being a bit more explicit could be nice.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    @FaneDuru - I guess you are missing something, the `c.Cells(10,1)` defines the low right cell of the `tgt` range. – Vityata May 07 '20 at 20:42
  • I mistakenly showed ```Dim ws1 As Range``` ... didn't make that mistake in the actual code. I also do have underscores in my actual named range. Apologies for the errors on my part, I've edited the post. – bhbennett3 May 07 '20 at 20:45
  • @bhbennett3 - that's strange, I was able to run your code, simply adding `Sub TestMe` on the top and adding the named range. Are you sure the named range is in `Sheet 1` or in another sheet? – Vityata May 07 '20 at 20:49
  • It is... I've used your method - ```Debug.Print tgt.Address``` - and confirmed that my ranges actually are assigning correctly. I think the error may actually be due to something else in the module. Bad question on my part, thank you for your help though. – bhbennett3 May 07 '20 at 20:53