Thread: duplicate listbox selection to range of cells?

Is is possible to amend the following macro, so that a listbox selection is copied to a range of consecutive cells? Yes that's right! For scheduling purposes I need exactly the same value copied to each cell. The macro listed below only copies the selection to the active cell. Any help appreciated

Sub Control_on_Worksheet()
Dim mypick As Variant
With Worksheets("Sheet1").DropDowns("my control")
mypick = .ListIndex
ActiveCell.Value = .List(mypick)
.Value = 0
End With
End Sub

You could get 12 consecutive cells with:

ActiveCell.Resize(12, 1).Value = .List(mypick)

But how do you know when to stop?

lozc wrote:
>
> Is is possible to amend the following macro, so that a listbox selection
> is copied to a range of consecutive cells? Yes that's right! For
> scheduling purposes I need exactly the same value copied to each cell.
> The macro listed below only copies the selection to the active cell. Any
> help appreciated
>
> Sub Control_on_Worksheet()
> Dim mypick As Variant
> With Worksheets("Sheet1").DropDowns("my control")
> mypick = .ListIndex
> ActiveCell.Value = .List(mypick)
> Value = 0
> End With
> End Sub
>
> --
> lozc
> ------------------------------------------------------------------------
> lozc's Profile: http://www.excelforum.com/member.php...o&userid=31340
> View this thread: http://www.excelforum.com/showthread...hreadid=510269

--

Dave Peterson

Thanks Dave,

That was certainly useful, but I am looking for the value to be duplicated to a set range of consecutive horizontal cells (eg A1:G1), rather than consecutively from the current active cell, any ideas?

Thanks Again

ActiveCell.Resize(1, 7).Value = .List(mypick)
or
ActiveSheet.Range("a1:g1").Value = .List(mypick)

lozc wrote:
>
> Thanks Dave,
>
> That was certainly useful, but I am looking for the value to be
> duplicated to a set range of consecutive horizontal cells (eg A1:G1),
> rather than consecutively from the current active cell, any ideas?
>
> Thanks Again
>
> --
> lozc
> ------------------------------------------------------------------------
> lozc's Profile: http://www.excelforum.com/member.php...o&userid=31340
> View this thread: http://www.excelforum.com/showthread...hreadid=510269

--

Dave Peterson

perfect! Thanks again