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

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.

John Alexiou · Accepted Answer · 2010-12-07 20:18:23Z

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.

edited Dec 7, 2010 at 20:18

answered Dec 7, 2010 at 16:07

John Alexiou

29.4k12 gold badges83 silver badges136 bronze badges

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

3 Comments

Chris Spicer Over a year ago

Very well spotted! You can indeed replicate the problem by activating a sheet other than "Payable".

rryanp Over a year ago

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

John Alexiou Over a year ago

I fixed the post. I forgot to reference the top left cell of the data.

Alex P · Accepted Answer · 2010-12-07 21:07:45Z

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

user2978925 · Accepted Answer · 2013-11-11 11:25:26Z

0

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

more lines, but work..

answered Nov 11, 2013 at 11:25

user2978925

1

Comments

bs0d · Accepted Answer · 2013-12-12 19:14:50Z

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

answered Dec 12, 2013 at 19:14

bs0d

261 bronze badge

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Linked

Related