Sheet Range copy - error on destination

[Guest]

The function below works in conjunction with a worksheet_change event and
some logic to scan various regions of the sheet to see exactly where the
change took place. Once I have that information, I simply need to pass it
the row number in a loop and grab the entire block of data that is revelant
to that particular change.

For example, A1:M15 as a range.

Since theres a significant number of these inventory blocks I want to copy
them to the Ready for Review sheet which then gets emailed automatically. I
am able to accurately copy the particular range, and even get it to the
destination correctly - but it only works on the Destination if the Range for
that is something like Range("A1"). This is confusing since the
Range(Cells(my_row,1)) works fine for selecting the range. Do I need to pass
it the actual size of the range when using Cells, or is there another way to
achieve this without setting Destination to "A1" or some other cell
reference, but by using Row # and Column #?

Thanks,
F. Eetch.

'************************
'************************
Function Report_Changes(my_row, my_dest_row)

Sheets("Inventory").Range(Cells(my_row, 1), Cells(my_row + 14, 13)).Copy _
Destination:=Sheets("Ready for Review").Range(Cells(my_dest_row,
1))


Worksheets("Ready For Review").Columns("A:Z").AutoFit

End Function

 

[Dave Peterson]

Try qualifying your ranges:

Function Report_Changes(my_row, my_dest_row)

Sheets("Inventory").Range(Sheets("Inventory").Cells(my_row, 1), _
Sheets("Inventory").Cells(my_row + 14, 13)).Copy _
Destination:=Sheets("Ready for Review").Cells(my_dest_row, 1)


Worksheets("Ready For Review").Columns("A:Z").AutoFit

End Function

I'd use a couple of variables so that it's easier:

Function Report_Changes(my_row, my_dest_row)

Dim RngToCopy as range
Dim DestCell as range

with Sheets("Inventory")
set rngtocopy = .Range(.Cells(my_row, 1), .Cells(my_row + 14, 13))
end with

with Sheets("Ready for Review")
set destcell = .Cells(my_dest_row, 1)
end with

rngtocopy.copy _
destination:=destcell

Worksheets("Ready For Review").Columns("A:Z").AutoFit

End Function

You could replace this:

with Sheets("Inventory")
set rngtocopy = .Range(.Cells(my_row, 1), .Cells(my_row + 14, 13))
end with

with:

with Sheets("Inventory")
set rngtocopy = .Cells(my_row, 1).resize(15,13)
end with

==========
An unqualified range will refer to the activesheet if the code is in a general
module. It will refer to the worksheet that owns the code if the code is behind
a worksheet.