Assistance resolving Runtime 1004 error

[stefanj]

Good afternoon,

I am currently working on an Excel template, and I am trying to copy cells down a dynamic range defined by the values of specific cells.

However, I am continually getting "runtime error 1004 ': method range of object '_ worksheet failed" messages, and I can't figure out what to do to resolve them.

My current code is this (it's supposed to be fired from another subroutine):


Private Sub CopyUnits()

If Range("AB22").Value - 1 = Range("AA22").Value Then Exit Sub


Dim UnitStartRow As Integer
Dim UnitEndRow As Integer
Dim Units As Range
UnitStartRow = Range("AA22").Value
UnitEndRow = Range("AB22").Value - 1
Set Units = Range(Cells(UnitStartRow, 4), Cells(UnitEndRow, 4))


Range(Cells(UnitStartRow, 4)).Copy Range("Units")

Range(Cells(UnitStartRow, 7)).Copy Range(Cells(UnitStartRow, 7), Cells(UnitEndRow, 7))


End Sub

Ideally, this code should only work for the active sheet in this workbook.

What am I doing wrong?

Currently using Microsoft Visual Basic for Applications 7.1 and Microsoft Excel for Office 365 version 1904.

Thank you very much.

[Aussiebear]

Is your code module located in the same sheet, otherwise try defining the sheet to which you would like this code to work on.

[Paul_Hossler]

There's a number of unknowns about your example

Can you make a small workbook with just enough code to show the error and attach it?


Meantime, if you do not have a Named Range (not same as a variable) this might be the issue



Set Units = Range(Cells(UnitStartRow, 4), Cells(UnitEndRow, 4))
Range(Cells(UnitStartRow, 4)).Copy Range("Units")



Did you mean this?




Set Units = Range(Cells(UnitStartRow, 4), Cells(UnitEndRow, 4))

Range(Cells(UnitStartRow, 4)).Copy Units ' <<<<<<<<<<<<<<<<<<<<<



although that will only copy one cell

[stefanj]

Is your code module located in the same sheet, otherwise try defining the sheet to which you would like this code to work on.

I'm...not entirely sure how to do that. My understanding is that the Cells...property?...defaults to the active worksheet, which is exactly what I want.


There's a number of unknowns about your example

Can you make a small workbook with just enough code to show the error and attach it?


Meantime, if you do not have a Named Range (not same as a variable) this might be the issue



Set Units = Range(Cells(UnitStartRow, 4), Cells(UnitEndRow, 4))
Range(Cells(UnitStartRow, 4)).Copy Range("Units")



Did you mean this?



Set Units = Range(Cells(UnitStartRow, 4), Cells(UnitEndRow, 4))

Range(Cells(UnitStartRow, 4)).Copy Units ' <<<<<<<<<<<<<<<<<<<<<



although that will only copy one cell



I've attached the relevent section of my template with the parts that the code is supposed to affect.

What I'm trying to do is expand the Rentable Area section (outlined in red) based on the number of unit numbers (separated by commas, which changes the value in the X column) entered into the Units field (orange). This part of the coding works perfectly.

Then I want to pull the formula in the yellow cell and the "sq.ft." in the green cell down into the expanded range, which is where things start going wrong.

Thank you very much for your assistance.

[大灰狼1976]

Hi stefanj!
The problem with this code: "Set Units = Range(Cells(UnitStartRow, 4), Cells(UnitEndRow, 4))",
because UnitStartRow and UnitEndRow did not get the correct values.

[stefanj]

Whoops. Yeah, I forgot to tweak the code in the example sheet for the revised cell references.

Here's the corrected version.

Thanks.