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.
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
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
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
Answered By – Siddharth Rout
Answer Checked By – Dawn Plyler (AngularFixing Volunteer)