bringing up a window to make header row selections

M

Matthew Dyer

So here's what I'm starting with and what I'd like to do with it.

I start with a large spreadsheet with values in several columns. I
have a macro that goes through and cuts out columns by specfic column
values (A, G, L thru Q, etc.). But, since the reporting department
responsible for maintaining this file doesn't do such a great job of
maintenance, this specific cut/paste by column isn't working any
longer.

What I'd like to do is have a window pop up with two fields. On the
left is a list of all the Headers found in Row 1. You can then select
which Headers you'd like to move over into the right hand side. When
the 'OK' button is pressed, the macro is executed and the un-selected
columns on the left hand side are cleared while the selected columns
are all re-organized and placed side by side with no blank columns in
between them. Can somebody please help me out on this?
 
P

Patrick Molloy

interesting
i assume first header is in A1 and that theer are no gaps in the headers

add a userform. place two listboxes (listbox1, listbox2) and a command button

double clicking either box will move the item selected to the other box.
order isn't relevent. clicking the command button will delete columns in
listbox1 - by default, everything else moves left, then adds back the removed
headers

Option Explicit
Private Sub CommandButton1_Click()
Dim index As Long
Dim cl As Long
'delete remaing columns in listbox1
With ListBox1
For index = 0 To .ListCount - 1
For cl = 1 To Range("A1").End(xlToRight).Column
If Cells(1, cl).Value = .List(index) Then
Columns(cl).Delete
Exit For
End If
Next
Next
' add back deleted headings
For index = 0 To .ListCount - 1
Cells(ListBox2.ListCount + index + 1) = .List(index)
Next
End With
Unload Me
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ListBox2.AddItem ListBox1.Value
ListBox1.RemoveItem (ListBox1.ListIndex)
End Sub



Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ListBox1.AddItem ListBox2.Value
ListBox2.RemoveItem (ListBox2.ListIndex)
End Sub

Private Sub UserForm_Initialize()


Dim cl As Long
With ListBox1
For cl = 1 To Range("A1").End(xlToRight).Column
.AddItem Cells(1, cl)
Next
End With

End Sub
 
M

Matthew Dyer

This is working BEAUTIFULLY! I'll tinker with it a little bit to get
it exactly how I'd like it, but this is exactly what I needed. Haha,
it took me a few minutes to figure out how to get the userform to pop
up.

I am always suprised how much help I can find here on google groups
but when trying to ask for help in a forum that you have to subscribe
to and all that jazz nobody seems to want to offer any assistance at
all.

THANKS AGAIN!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top