Kapat

Excel Copy Columns With Userform

excel invoice Move Items Between List Boxes

In this tutorial, we have prepared a template where we can copy columns from a sheet to other sheet using userform. We have a workbook consisting of Database and Report sheets.

We placed 2 list boxes on the userform. Column headers of Database sheet that contained data are listed on Listbox1 with the following VBA codes in UserForm_Initialize procedure : vba list column headers on listbox

Dim sutn, lst_column As Integer
lst_column = Sheets("Database").Cells(1, Columns.Count).End(xlToLeft).Column
For sutn = 1 To lst_column                 
 ListBox1.AddItem Sheets("Database").Cells(1, sutn).Value
    If Sheets("Database").Columns(sutn).Hidden = True Then
 ListBox1.Selected(sutn - 1) = True
    End If
Next

lst_column = Sheets("Database").Cells(1,Columns.Count).End(xlToLeft).Column vba find used last column On Database sheet, we learn the last used column number with these codes and assign it to lst_column variable.  In Database sheet, this number is 12 and it indicates column L.

We need to move columns (column headers) that we wanted to copy, from ListBox1 to ListBox2. In the userform, we used the buttons to move items between List boxes.

excel vba listbox move item

The codes of button (CommandButton5) that we pressed to move an item from Listbox1 to Listbox2 are as follows : excel move from listbox to listbox

Private Sub CommandButton5_Click()
Dim deger As String, m As Integer
If ListBox1.ListIndex = -1 Then   'If there is no item selected on listbox,no move will be made.
MsgBox "Choose an listbox item from left", , ""
Exit Sub
End If

deger = ListBox1.Value
For m = 0 To ListBox2.ListCount - 1
    If deger = CStr(ListBox2.List(m)) Then
        MsgBox "This item already exists in ListBox2", vbCritical, ""
    Exit Sub
    End If
Next
ListBox2.ListIndex = -1
 ListBox2.AddItem ListBox1.Value
ListBox1.RemoveItem (ListBox1.ListIndex)
Call animation_to_right
End Sub

deger = ListBox1.Value
For m = 0 To ListBox2.ListCount - 1
If deger = CStr(ListBox2.List(m)) Then
MsgBox "This item already exists in ListBox2", vbCritical, ""
Exit Sub
End If
Next
vba if item already exist on listbox With this loop, it is checked whether the ListBox1 item that we want to move is in ListBox2. If there is already this item in ListBox2, the item isn’t moved. When moving an item from ListBox2 to ListBox1, it is controlled with VBA loop in the same way.

Also, items can be moved from Listbox2 to Listbox1. The selected item in Listbox2 can be moved up or down by pressing the spin button. excel move from listbox to listbox

excel vba listbox move item up down

We added the following codes to the spin button to move items up and down : excel move up down listbox item

Private Sub SpinButton1_SpinDown()
With ListBox2
If .ListIndex = -1 Then Exit Sub
    If .ListIndex < .ListCount - 1 Then
         .AddItem .List(.ListIndex), .ListIndex + 2
            .RemoveItem .ListIndex
            .ListIndex = .ListIndex + 1
    End If
End With
End Sub

Private Sub SpinButton1_SpinUp()
With ListBox2
If .ListIndex = -1 Then Exit Sub
    If .ListIndex > 0 Then
        .AddItem .List(.ListIndex), .ListIndex - 1
        If .ListCount - 1 = .ListIndex Then
            .RemoveItem .ListIndex
            .ListIndex = .ListIndex - 1
        Else
            .RemoveItem .ListIndex
            .ListIndex = .ListIndex - 2
        End If
    End If
End With
End Sub

vba button mousemove We added the codes that trigger the MouseMove event for the 3 buttons on the userform. The image is similar to CSS hover effect.

MouseMove procedure for top button (CommandButton5) :
Private Sub CommandButton5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton5.Width = 38
CommandButton5.Left = 150
End Sub

We have added the MouseMove procedure for the userform so that the button returns to its default location and width when the mouse leaves the button:
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
CommandButton5.Width = 29
CommandButton5.Left = 150

After column headers of Database sheet are sorted on ListBox2, the listed columns in ListBox2 are copied to Report sheet by clicking the Filter button. Report sheet cells are cleared before copying. excel copy columns to other sheet

excel copy columns

VBA Codes of Filter button : excel advanced filter

For basliklar = 0 To ListBox2.ListCount - 1
baslangic_satiri = 2
Sheets("Report").Cells(baslangic_satiri - 1, basliklar + 1) = ListBox2.List(basliklar, 0)

Sheets("Database").Range(FirstCell, LastCell).AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Sheets("Database").Range(FirstCell, LastCell), _
    CopyToRange:=Sheets("Report").Cells(baslangic_satiri - 1, basliklar + 1), _
    Unique:=False
Next
Sheets("Report").Columns.EntireColumn.AutoFit   'Widths of columns are set.

lst_column = Sheets("Report").Cells(1, Columns.Count).End(xlToLeft).Column
For s = 1 To lst_column         'Background color of column headers
Sheets("Report").Cells(1, s).Interior.Color = RGB(218, 238, 243)
Sheets("Report").Cells(1, s).Font.Bold = True
Next

vba progress bar The animated progress bar is showed before columns copying to Report sheet. Progress bar is run on other userforms. excel progress bar

📥 Download sample workbook