Display a range of cells

[Tanner_2004]

I am using offset to find the header of a column that has several cell below it. I simply want to select the header or top field (which I now how to do), and the copy and paste all cell values to another range of cells. The cells that I want to copy of directly below the header or top field that has been selected.

Thank you.

PS. The idea is to use a down to find the name of a craft project, then return a shopping list of the protect items needed.

Thanks again!

 

[CalcSux78]

Add a worksheet_selectionchange event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Dim lRow As Long
Dim Col As Long     'target column

'if you want to watch all cells in row 1
If Target.Row = 1 Then
    lRow = Cells(Rows.Count, Target.Column).End(xlUp).Row
    Col = Target.Column
    If lRow > 1 Then
        Range(Cells(2, Col).Address, Cells(lRow, Col).Address).Copy Sheets(2).[A1]
        'change destination portion to actual location
    End If
End If
End Sub

 

[Tanner_2004]

Thank you. I will give this a try.