VBA method 'range of object' _Worksheet failed variables used in range declaration

Question

I am getting the following error message: Method range of object _worksheet failed when trying to select a range in excel using variables as range length.

Below is a snippet of my code:

Private Function copyAmount(startRange As Integer, endRange As Integer)
    Dim startRng As String
    Dim endRng As String

    startRng = "A" & Str(startRange)
    endRng = "A" & Str(endRange)

    activateBook ("book2.xlsm")
    Set rng = Range(startRng, endRng)
    Workbooks("book2.xlsm").Sheets(1).Range(rng).Select
    Selection.Copy
    activateBook ("Book1.xlsm")
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Function

Any help would be greatly appreciated.

The particular error you have won't happen if you replace Range(rng).Select with rng.Select (i.e. it is already a Range) assuming rng has been defined as a Range variable in MS Excel 2010. — ecoe
– ecoe, Commented Apr 4, 2014 at 17:00

Nimantha · Accepted Answer · 2022-01-07 10:22:06Z

7

You are getting the error because you are not fully qualifying your ranges. Also it is not necessary to activate a workbook to do a copy paste :) Also you do not need a Function for this. Use a Sub

CODE

Private Sub copyAmount(startRange As Integer, endRange As Integer)
    Dim wbT As Workbook, wbO As Workbook
    Dim rng As Range
    
    Set wbT = ThisWorkbook
    Set wbO = Workbooks("book2.xlsm")
    
    Set rng = wbO.Sheets(1).Range("A" & startRange & ":" & "A" & endRange)
    rng.Copy
    
    '~~> Change Sheets(1) below to the relevant sheet
    wbT.Sheets(1).Range("D3").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

EDIT

Sub, like a Function procedure, is a separate procedure that can take arguments, perform a series of statements, and change the value of its arguments. However a Sub procedure doesn't return a value like a Function does.

edited Jan 7, 2022 at 10:22

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Jul 3, 2012 at 9:18

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

Comments

Siddharth Rout · Accepted Answer · 2013-04-27 14:49:14Z

0

Instead of Str, use Cstr. This will make your code work I guess...

edited Apr 27, 2013 at 14:49

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

answered Jul 3, 2012 at 9:16

Trace

18.9k21 gold badges98 silver badges174 bronze badges

Comments

Matt · Accepted Answer · 2015-11-24 22:23:50Z

0

You will get this error when your code is in a worksheet and not a module.

edited Nov 24, 2015 at 22:23

Matt

75.4k26 gold badges156 silver badges181 bronze badges

answered Nov 24, 2015 at 20:45

IndyThinker

1

Collectives™ on Stack Overflow

VBA method 'range of object' _Worksheet failed variables used in range declaration

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related