-
How to change macro?
I have this makro which works fine but I have a problem when user select two or more item in listbox. So I need to change macro so when user select two or more item to make copies and paste them on sheet1 and starting with row 24.
Private Sub CommandButton1_Click()
If ListBox1.Value = "Delta" Then
Worksheets("vj").Range("4:4").Copy
With Worksheets("Sheet1").Range("24:24")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
ElseIf ListBox1.Value = "Alfa" Then
Worksheets("vj").Range("8:8").Copy
With Worksheets("Sheet1").Range("24:24")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
If ListBox1.Value = "Eta" Then
Worksheets("vj").Range("12:12").Copy
With Worksheets("Sheet1").Range("24:24")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
If ListBox1.Value = "Gamma" Then
Worksheets("vj").Range("16:16").Copy
With Worksheets("Sheet1").Range("24:24")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
If ListBox1.Value = "Omega" Then
Worksheets("vj").Range("20:20").Copy
With Worksheets("Sheet1").Range("24:24")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteComments
End With
-
Is that supposed to be working code?
-
Sorry but I am just novice in vba.
-
See if this does what you want
[vba]
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
[/vba]
-
Yes that is exactly what I wanted. Thank you very much.
One more question
What to change in macro If I want copy range a5:a8 instead of row and paste them in columns. Starting with column a and row 3.
-
I have also one more problem. How to change macro if I have four listbox.