Copy data from list box to sheets
 

Hi,
Im trying to get the data that I retrieve from a list box to copy to sheet
2 (€œFall)in Excel2007. Currently, the code works correctly for populating
sheet1, but I only want columns A & B to appear on sheet2. The code below was
supplied by Ron de Bruin, that I have tweaked to fit my variables. Can anyone
help me with making this work? Thanks.
Mark

Private Sub CommandButton1_Click()
Dim RowNdx As Long
Dim ColNdx As Integer
Dim SaveColNdx As Integer
Dim wkbk As ThisWorkbook
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ColNdx = 1
RowNdx = 13

For X = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(X) = True Then
Cells(RowNdx + 1, ColNdx).EntireRow.Insert
Rows(RowNdx).Copy
Rows(RowNdx + 1).PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
Cells(lastrow + 1, ColNdx).Select
' display the Selected item.
Cells(RowNdx, ColNdx).Value = ListBox1.List(X)
If Worksheets(1).Name < "Fall" Then

For t = 2 To ActiveWorkbook.ActiveSheet.UsedRange.Columns.Count
With Cells(RowNdx, t)
.Formula = "=vlookup(" & Cells(RowNdx, ColNdx).address _
& ",'" & studentPath & "[" & studentFile & "]" & grade &
"'!A:Z," & t & ", 0)"
.Value = .Value
End With
Next t
With Worksheets("Fall")
.Cells(RowNdx + 1, ColNdx).EntireRow.Insert
.Cells(RowNdx, ColNdx).EntireRow.Copy
.Cells(RowNdx + 1, ColNdx).EntireRow.PasteSpecial Paste:=xlFormats
Application.CutCopyMode = False
.Cells(RowNdx, ColNdx).Value = ListBox1.List(X)
End With

End If
RowNdx = RowNdx + 1
End If
Next X
If Worksheets(1).Name < "Fall" Then
Rows(RowNdx).Delete Shift:=xlUp
End If
Worksheets("Fall").Rows(RowNdx).Delete Shift:=xlUp
Unload Me
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub