Run-time error '1004' when trying to copy a range (Excel VBA)

Question

I am trying to copy a range of cells for which I don't know the last row and column (though I can easily get to those using variables). Unfortunately though, the way I am trying to reference a range is giving me a run-time error 1004 (Application-defined or object-defined error) when I use variables, and I can't figure out why. Below is a sample of the code:

Dim wkbk As Workbook
Dim copy_rng As Range
...
Set copy_rng = wkbk.Worksheets("Payable").Range("A1:Y3500")
Set copy_rng = wkbk.Worksheets("Payable").Range(Cells(1, 1), Cells(last_row_pay, last_col_pay))

The first Set statement is simply an example, and it works fine (so I know wkbk is properly defined and it's finding the "Payable" worksheet). Does anybody know why the second Set statement would not work? Is there a syntax issue? (During debug, if I hover over the last_row_pay and last_col_pay variables, I can see valid values--1533 and 25 respectively.) Thanks for any help.

Answer 1

Use the resize function, because the Cells() function works for the active worksheet and it might get confused.

Dim wkbk As Workbook
Dim copy_rng As Range 
Dim pay_rows As Integer ,pay_columns As Integer
... 
pay_rows = 3500
pay_columns = 23
Set copy_rng = wkbk.Worksheets("Payable").Range("A1").Resize(pay_rows,pay_columns)

where A1 is the top left cell of your data.

Answer 2

If the range that you are trying to copy is a contiguous set of cells then I find the easiest way to get the size of the range without hard coding any row / column counts is to use CurrentRegion

Sub GetCurrentRange

Dim rng as range
Set rng = Worksheets("Payable").Range("A1").CurrentRegion

End Sub

The advantage here is that even if you add rows / columns to your dataset you don't need to bother working out the new column and row limits as CurrentRegion does this for you

Example:

     A     B     C
 1   10    20    30
 2   40    50    60
 3   70    80    90

Sub GetCurrentRange

Dim rng as range
Set rng = Worksheets("Payable").Range("A1").CurrentRegion

Debug.Print rng.Address //Prints $A$1:$C$3

End Sub

Answer 3

with Worksheets("Payable")
  copy_rng = .Range(.Cells(1, 1), .Cells(last_row_pay, last_col_pay)).Value
end with

more lines, but work..

Answer 4

I had the same issues. You need to qualify both cell and range properties.

Set copy_rng = wkbk.Worksheets("Payable").Range(Cells(1, 1), wkbk.Sheets("Payable").Cells(last_row_pay, last_col_pay))