1004 error (select method of range class failed)

Question

I am currently working on a tool, getting some data but not the formula or other format. Just the plain text. However it gives me the error of 1004.

here's my code:

Sub WBS()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Workbooks("totalcosts.xlsm").Worksheets(3).Range("A3:A300")
    Set targetColumn = Workbooks("Backing sheet.xlsm").Worksheets(2).Range("C6:C300")

    sourceColumn.Copy
    targetColumn.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    Application.CutCopyMode = False
    Call Resource_Name
End Sub

It always give me error on the targetcolumn.select

But when I use this code to copy and paste on the line,

*"sourceColumn.Copy destination=targetcolumn"*

It paste the data that I need. However it copies the formula instead of the value.

score 2 · Accepted Answer · edited May 23 '17 at 12:15

2

Cut your destination down to a single cell (top-left) and let the paste operation fill the remainder.

Sub WBS()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Workbooks("totalcosts.xlsm").Worksheets(3).Range("A3:A300")
    Set targetColumn = Workbooks("Backing sheet.xlsm").Worksheets(2).Range("C6")

    sourceColumn.Copy
    targetColumn.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                              SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    Call Resource_Name
End Sub

Use the Range.PasteSpecial method directly off the target range. There is no need to Range .Select method it first¹.

Since you are only transferring the Range.Value property, a direct value transfer would be the better option.

Sub WBS()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Workbooks("totalcosts.xlsm").Worksheets(3).Range("A3:A300")
    Set targetColumn = Workbooks("Backing sheet.xlsm").Worksheets(2).Range("C6")

    with sourceColumn
        targetColumn.Resize(.Rows.Count, .Columns.Count) = .Value
    end with

    Call Resource_Name
End Sub

¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

edited May 23 '17 at 12:15

Community

1
1

answered Feb 15 '16 at 06:50

what I did is just remove the targetcolumn.select :) – deejay Feb 15 '16 at 07:29
Yes, you cannot use the [Range .Select](https://msdn.microsoft.com/en-us/library/office/ff197597.aspx) method on a cell that is not in the [ActiveSheet property](https://msdn.microsoft.com/en-us/library/office/ff822753.aspx?f=255&MSPPError=-2147217396). You were probably focused on the worksheet holding the `sourceColumn` cells. Best practise is to avoid it unless absolutely necessary. – Feb 15 '16 at 07:32
hello jeeped thank you very much.. but I have added some question above :) I hope you can help me too with that :) – deejay Feb 15 '16 at 08:13
I believe this question should be left closed off and you can start a new question with the updated information and new inquiry. See [Etiquette for Russian Doll Questions](http://meta.stackexchange.com/questions/188625/etiquette-for-russian-doll-questions). – Feb 15 '16 at 08:20
im so sorry I did not know that rules in here :( BTW I already removed the added querry. Thanks again for answering :) – deejay Feb 15 '16 at 08:36

1004 error (select method of range class failed)

1 Answers1