stefanj
05-31-2019, 04:54 PM
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.
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.