Help please if possible. I have 3 columns of data on a worksheet 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 worksheet, 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 worksheet 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...o&userid=29347
View this thread: http://www.excelforum.com/showthread...hreadid=490566

tvac,

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 worksheet 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 worksheet, 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 worksheet 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...o&userid=29347
View this thread: http://www.excelforum.com/showthread...hreadid=490566

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
sheet, 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 sheet 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 sheet 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 worksheet
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 worksheet, 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 worksheet 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...o&userid=29347
View this thread:
http://www.excelforum.com/showthread...hreadid=490566




--
tvac
------------------------------------------------------------------------
tvac's Profile: http://www.excelforum.com/member.php...o&userid=29347
View this thread: http://www.excelforum.com/showthread...hreadid=490566

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.ListInde x + 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
sheet, 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 sheet 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 sheet 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 worksheet
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 worksheet, 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 worksheet 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...o&userid=29347
View this thread:
http://www.excelforum.com/showthread...hreadid=490566




--
tvac
------------------------------------------------------------------------
tvac's Profile: http://www.excelforum.com/member.php...o&userid=29347
View this thread: http://www.excelforum.com/showthread...hreadid=490566

Art
Great stuff. I tweeked a few things, but it works! Thanks for your
help. Regards John


--
tvac
------------------------------------------------------------------------
tvac's Profile: http://www.excelforum.com/member.php...o&userid=29347
View this thread: http://www.excelforum.com/showthread...hreadid=490566