Copy paste after selection in listbox

[Alexon2008]

As it is now this macro after multiply selection in listbox copy rows from sheet (VJ) and paste dem on sheet1. I Want to copy ranges instead of rows. Please some help.

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

 

[PHV]

Simply chage this:
Worksheets("vj").Rows(mpRow).Copy
using a range instead of Rows.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Alexon2008]

one more question: what to change in this part of macro so when I want to paste two ranges so they stand beside each other and not beneath each other.

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

 

[PHV]

Use the macrorecorder when doing what you want manually and then eamine the generated code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Alexon2008]

This can't be recorded. I need to paste ranges beside each other and not beneath each other.

 

[PHV]

How do you that manually ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

"This can't be recorded"

How's that???

Check out the End(xlToRight) method.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}