Copy paste

[Alexon2008]

I have four listbox and when user select items in listbox then macro copy some range in sheet vj and paste them on Sheet1.
But I have to problems with this macro:

1. I want to copy range instead of row
2. How to change macro if I have two or more listbox.


Private Sub CommandButton1_Click()
Dim mpRow As Long
Dim i As Long

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Select Case .List(i)

Case "Delta": mpRow = 4

Case "Alfa": mpRow = 8

Case "Eta": mpRow = 12
Case "Gamma": mpRow = 16

Case "Omega": mpRow = 20
End Select

Worksheets("vj").Rows(mpRow).Copy

With Worksheets("Sheet1")
With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)

.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
End With
End If
Next i
End With

End Sub

[Alexon2008]

I?ve got some help on another forum but there is still one problem. As it is now it si only possible to select items from listbox1 but I have also listbox2, 3 and 4. Please some help!



Private Sub CommandButton1_Click()
CopyIt
End Sub

Sub CopyIt()

Dim mpRange As Rang
Dim i As Long

With Me.ListBox1
For i = 0 To .ListCount - 1
If .Selected(i) Then
Select Case .List(i) 'using various range objects (http://vbaexpress.com/forum/)
Case "Delta": mpRange = Cells(4, 1) 'cell A4
Case "Alfa": mpRange = Range("A8")
Case "Eta": mpRange = MyCell 'a named range
Case "Gamma": mpRange = Range("A16:A19")
Case "Omega": mpRange = Range(Cells(20, 1), Cells(20, 2)) '= Range("A20:B20)
End Select
Worksheets("vj").mpRange.Copy
With Worksheets("Sheet1")
With .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
End With
End If
Next i
End With