0

I am getting stuck between two errors. I am trying to create a module to copy data from one tab of a workbook and then paste it into another. When I run code A:

Dim N As Range
Dim Out As Range
Dim c As Range
Dim StartCell As Range

Set StartCell = Worksheets("Sheet 2").Range("A1")
Set c = Sheets("Sheet 1").Range("Named_Range").Cells(1, 1)
Set N = Sheets("Sheet 1").Range(c, c.Cells(10, 1))         'Using "c" here to select a dynamically chosen portion of a named range, "10" chosen for example

Set Out = StartCell.Offset(, 2)                           

'Copy / paste
Worksheets("Sheet 1").N.Copy (Worksheets("Sheet 2").Out)

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

But when I run code B:

Dim N As Range
Dim Out As Range
Dim c As Range
Dim StartCell As Range

Set StartCell = Worksheets("Sheet 2").Range("A1")
Set c = Sheets("Sheet 1").Range("Named_Range").Cells(1, 1)
Set N = Sheets("Sheet 1").Range(c, c.Cells(10, 1))         'Using "c" here to select a dynamically chosen portion of a named range, "10" chosen for example

Set Out = StartCell.Offset(, 2)                           

'Copy / paste
N.Copy (Out)

I get Run-time error '1004': Copy method of Range class failed

My understanding is that Code A isn't working because of something to do with "late binding".

I believe code B isn't working because I need to select the relevant sheets.

Additionally, I'll note that when I try to specify Set Out = Worksheets("Sheet 2").StartCell.Offset(, 2) I am also getting a heavy dose of error '438'. I cannot understand why, other than that I need to do Worksheets("Sheet 2").Select before doing my Set Out but again I understand that it's not a best practice to rely on Select that way.

What is an Excel wonk to do here? I feel like good ol' MS has me cornered between a rock and a hard place here.

bhbennett3
  • 123
  • 8

1 Answers1

1

In your first piece of code:

Set Out = Range("A1").Offset(, 2) create a range in Active Sheet.

Worksheets("Sheet 1").N.Copy does not have any meaning...

If you want to copy N range, you can do that in the next way:

Replace Set In = with Set N = Sheets("Sheet 1").Range(c, c.Cells(10, 1))

N.Copy Destination:=Worksheets("Sheet 2").Range("A1").Offset(, 2) 'or Range("C1")

In the second piece of code you simple use:

N.Copy Destination:=Out

To set your Out range in Sheet2, you simply do that:

Set Out = Worksheets("Sheet 2").Range("A1").Offset(, 2)

And then,

 N.Copy Destination:=Out
FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • Apologies, I made a mistake in my post... I am using ```Set N =``` not ```Set In =``` – bhbennett3 May 07 '20 at 22:28
  • I will try using ```Destination:=``` however and let you know if that resolves my issue. Do you know how I can ensure that my ```Set Out = ``` statement creates a range on Sheet 2? Per my post, I get an error when trying to do that. – bhbennett3 May 07 '20 at 22:30
  • @bhbennett3: Updated the code for `Out` range definition in `Sheet2'. – FaneDuru May 07 '20 at 22:55
  • Maybe I was unclear... when I add ```Worksheets("Sheet 2")``` I also get Run-time error '438'. The following line of code seems to be generating an error for me: ```Set Out = Worksheets("Sheet 2").Range("A1").Offset(, 2)``` – bhbennett3 May 08 '20 at 00:10
  • @bhbennett3 This is more then strange... Theoretically, you may receive an error only if a sheet named "Sheet 2" does not exist in the active workbook. But, in such a case, the error would be different (Subscript out of range). The error you mention is raised only if a property or a method does not exist for the used object and it is not the case. Maybe you are talking about a different error, or a different line where the error is raised and, please, check the existence of "Sheet 2". Maybe "Sheet2"... Or you miss-write something on this code line. Please edit question post and post your code – FaneDuru May 08 '20 at 08:34
  • I think I know the problem. I was not actually using `Range("A1")` in my code. I did not think it was pertinent to include, but it turns out that this was the issue... `Range("A1")` is actually a variable range called `StartCell` defined by `Set StartCell = Worksheets("Sheet 2").Range("A1")`. I believe the property/method error was being raised because I was referring to Sheet 2 twice. – bhbennett3 May 08 '20 at 14:52
  • @bhbennett3: If you are referring to something like `Worksheets("Sheet 1").N...`, of course you could receive such an error. `N` is not a worksheet property. It is a range already having the scope in that specific sheet... It is recommended to use the code like it is and **only after testing it** to try adapting somehow. In such a case you can be sure that it works like it is. So, does my code answer your question? – FaneDuru May 08 '20 at 16:12