My code does exactly what you say. The value of 1 +
Sheets("Sheet1").Cells(r3,
"BU") will vary depending on what is in column BU. If it = 1, Sheet2
will be used. If it = 2, Sheet3 will be used, etc. If you want to
copy from multiple rows, then put my code within a loop.

Hth,
Merjet