Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vb code to copy and list selected row to diff sheets | Excel Discussion (Misc queries) | |||
Copy data to different sheets | Excel Worksheet Functions | |||
Copy from various sheets and create a master list. | Excel Programming | |||
Copy multiple sheets using a list box | Excel Programming | |||
in VBA Sheets("mysheet").Copy Befo=Sheets(1) how do i get a reference to the newly created copy of this sheet? | Excel Worksheet Functions |