(Solved) – Setting range on other sheet, based on abstract row number


With a sub on sheet1, I need to get data from a range of cells (P.. to W..) from row X, where X is take from a reference cell on sheet2.

Dim final_range as Range
Dim ref_cell_range as Range
Dim ref_cell as Range

ref_cell_range = Worksheets("sheet2").Range(O9:O15)

for each ref_cell in ref_cell_range
     Set final_range = Worksheets("sheet2").Range(Cells(ref_cell.Row, "P"), Cells(ref_cell.Row, "W"))

This code works if I leave out the ‘Worksheets(“sheet2”).’ part, but then it takes the cells from sheet1. If I run it like this, I get an error (‘Run-time error ‘1004’: Application-defined or object-defined error’)

Does anybody know what I might be doing wrong?

Leave a Reply

Your email address will not be published. Required fields are marked *