1

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.

Community
  • 1
  • 1
rryanp
  • 1,027
  • 8
  • 26
  • 45

4 Answers4

4

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.

John Alexiou
  • 28,472
  • 11
  • 77
  • 133
  • Very well spotted! You can indeed replicate the problem by activating a sheet other than "Payable". – Chris Spicer Dec 07 '10 at 16:29
  • I tried this, and now I get a Run-time error '438': Object doesn't support this property or method (on the Set line above). I'll look into that, but let me know if you have any thoughts. Thanks!!! – rryanp Dec 07 '10 at 16:42
  • I fixed the post. I forgot to reference the top left cell of the data. – John Alexiou Dec 07 '10 at 20:18
0
with Worksheets("Payable")
  copy_rng = .Range(.Cells(1, 1), .Cells(last_row_pay, last_col_pay)).Value
end with

more lines, but work..

user2978925
  • 1
0

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))

bs0d
  • 26
  • 1
0

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
Alex P
  • 12,249
  • 5
  • 51
  • 70