Re: Select range & copy

tvac,

Okay, the code now looks like this:

Private Sub ListBox1_Click()
Dim mRef As Integer
Dim mCol As Integer
mRef = Range("book2.xls!mRefList").Item(ListBox1.ListIndex + 1)
With Sheets("Sheet3")
mCol = .Range("A1:D1").Find(mRef).Column
End With
With Sheets("Sheet1")
.Range(.Cells(1, mCol), .Cells(4, mCol)).Copy
End With
Sheets("Sheet2").Range("A1:A4").PasteSpecial Paste:=xlPasteAll
End Sub

My columns are in sheet3.
The range to the left of the ListBox stuff is mColList
I changed the range name of the ListBox stuff to mRefList

mColList contains values that are in cells A1:D1 in sheet3.

I hope this helps.

Art


"tvac" wrote:

>
> Art or anyone.
> Thanks for this. I thought with my limited knowledge that I could make
> it work given a bit of a lead. However, in trying to keep it simple I
> omitted some info.
> The ListFillRange of the listbox refers to a list of names on another
> ***, against which is a ref number. I scroll through the list and
> click on the name required. (At this point the macro is activated and
> the first 2 columns of data are copied over). Also through a Vlookup
> the ref number for the name selected is placed in a cell next to the
> listbox. So I now need to continue the macro to :
> 1 - Read the value of the ref number in the cell next to the listbox.
> 2 - Go to the second *** containing the columns of data.
> 3 - Find the ref number in the row above the columns of data.
> 4 - Select the data range from that column.
> 5 - Copy and paste back to the first *** into the third column.
> Sorry to be a pain, but any further help would be appreciated.
> Regards - John
>
>
> I tried this:
>
> I put a ListBox on Sheet2 -- ListBox1.
> I put 4 values in J4:j8, also in sheet2.
> I named those 4 cells as mSource.
> I put mSource in the ListFillRange of the ListBox.
>
> I put 4 columns, each with 4 values in Sheet1.
> I put them in cells A1:D4
>
> I put the following code in the click event of ListBox1:
>
> Private Sub ListBox1_Click()
> Dim mCol As Integer
> mCol = ListBox1.ListIndex + 1
> With Sheets("Sheet1")
> .Range(.Cells(1, mCol), .Cells(4, mCol)).Copy
> End With
> Sheets("Sheet2").Range("A1:A4").PasteSpecial Paste:=xlPasteAll
> End Sub
>
> I think this does what you're looking for.
>
> Good luck with your project.
>
> Art
>
> "tvac" wrote:
>
> >
> > Help please if possible. I have 3 columns of data on a work***
> that
> > need to be updated when clicking on a selection in a list box.
> > The first 2 columns have common data regardless of the selection. I
> > have created a macro for this by following key strokes for select,
> copy
> > and paste, and it works fine.
> > The third column however requires data specific to the selection
> made
> > in the list box. This data is in columns on another work***, one
> > column for each possible selection option, and each column having a
> > reference number (1 to 20) across the top. These reference numbers
> > correspond to the options displayed in the list box.
> > How do I code the next part of the macro so that when clicking the
> > selection in the list box the corresponding reference number is
> > identified on the work*** containing the data, the data range
> > selected in that column, and copied to the data range in the third
> > column? Regards - John
> >
> >
> > --
> > tvac
> >
> ------------------------------------------------------------------------
> > tvac's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=29347
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=490566
> >
> >
>
>
> --
> tvac
> ------------------------------------------------------------------------
> tvac's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29347
> View this thread: http://www.excelforum.com/showthread.php?threadid=490566
>
>
.

Loading