excel - copy row range based on cell value

[cc4mail]

This has come up a few times in the forum without an answer (eg thread68_412048). I have the same problem.

Based on a cell value on sheet1, I want to copy a specific row range on sheet2 to sheet1 at a selected location.

The logic is correct, but I'm having a problem with the range. Can u help?

I want to call CopyRow onChange of specific cells on sheet1
(b21,b23,b25,b27,b29) and replace rows relative to the changed cell..



Sub CopyRow()
Dim varRow As Integer
Dim varCol As Integer
Dim varSel As Integer

varRow = Selection.row
varSel = Selection.Value
varCol = 3

' valid values 1 thru 7

Worksheets("Sheet2&quot.Select
Select Case varSel
Case 1
Worksheets("Sheet2&quot.Range("B6", "AD6&quot.Copy
Case 2
Worksheets("Sheet2&quot.Range("B8", "AD8&quot.Copy
Case 3
Worksheets("Sheet2&quot.Range("B10", "AD10&quot.Copy
Case 4
Worksheets("Sheet2&quot.Range("B12", "ADd12&quot.Copy
Case 5
Worksheets("Sheet2&quot.Range("B14", "AD14&quot.Copy
Case 6
Worksheets("Sheet2&quot.Range("B16", "AD16&quot.Copy
Case 7
Worksheets("Sheet2&quot.Range("B18", "AD18&quot.Copy
Case Else
MsgBox ("Incorrect value&quot
End Select

Worksheets("Sheet1&quot.Select
Worksheets("Sheet1&quot.Cells(varRow + 1, varCol).Select
Worksheets("Sheet1&quot.Paste
End Sub