Reference 5 cells offset from another for copying.
 

Hi,
The following allows me to address the start of 5 cells I want to copy to
another sheet.
How do I correctly address those 5 cells, which are offset 5 places form the
cell which matches a string on the other sheet, and then paste it to the
sheet, 20 places horizontally from the cells which matches the string on the
previous sheet.

I'm using a match function to locate cells on two sheets then trying to copy
5 cells from one sheet to the other sheet but the location is different.
The five cells which need to be copied are in columns "J to N" on one sheet
which uses Column "E"as reference point and Columns "Z to AD" on the other
sheet which also uses Column "F" .

z = ""
z = cell.Value 'set object from Match function

Set rng3 = Sheet2.Range("E2:E100").Find(what:=z) 'find the value in
sht2
Set rng5 = rng3.Offset(0, 5) '0 cells down 5 cells across


Set rng4 = Sheet1.Range("F2:F65536").Find(what:=z) 'find the correct
cell
Set rng6 = rng4.Offset(0, 20) ' 0 cells down 5 cells across
rng5.Copy Destination:=Sheet2.Range(rng6.Address) '.PasteSpecial '
copy the value

Please help.
--
Regards
Bill