Receiving error codes with using user form to rearrange and remove columns

I am just a dynamic userform... so, want it to be able to
remove columns/rearrange them
filter by criteria with checkboxes, then have the option to filter by date ranges for those columns that have dates, etc.
Option to build your own pivot table with the area for them to choose which filters to add where.

I have a lot of it well under way.. but ive hit a road block at the column deleting/sorting. The form I am having trouble with the code is the one titled "removereorder"
It is bringing over the correct columns but it is only bringing over the header...

any help or suggestions would be very much appreciate!

forum.ozgrid.com/index.php?attachment/68994/

Re: Receiving error codes with using user form to rearrange and remove columns

I sent the workbook - if you press control d to open the userform, then click on "open column editing tool", then move some columns, then press filter

***Note - the only tab that can be on the workbook is sheet1 and then results will go into sheet 2, which is created at the beginning of this particular sub

Re: Receiving error codes with using user form to rearrange and remove columns

its not that its an error - its that it is not copying the entire column.. its copying row 1 only... also, there is an 'error on resume next' line in there because for some reason autofilter doesn't like tables... it ends up converting it back to a range on its own so I just added a line to remove the table at the beginning.. and the on error resume next in case its not a table yet.

Re: Receiving error codes with using user form to rearrange and remove columns

Well, my original thought was to also have the functionality to both filter out criteria as well as rearrange/remove columns (then export to sheet2) but I couldnt figure out how to do both at once... so i guess my question is do you know how to do that? And if not, what do I need to change to just rearrange/move only the selected columns? Below is the full code for that userform... (minus the trigger in the module..)

Private Sub ColumnArrange_Click()On Error Resume Next
Sheet1.Unprotect
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Sheets("Sheet1")
Dim rng As Range
Set rng = ws.Range("$a1").CurrentRegion
Sheets.Add.Name = "Sheet2"
Set ws2 = Sheets("Sheet2")
ws.ListObjects("Data").Unlist
If LB2.ListCount = 0 Then
MsgBox "You did not choose a filter field"
Exit Sub
End If
ProgressDlg.Show
For basliklar = 0 To LB2.ListCount - 1
baslangic_satiri = 2
ws2.Cells(baslangic_satiri - 1, basliklar + 1) = LB2.List(basliklar, 0)
ws1.Cells(1, 1).CurrentRegion.Select.AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=rng.Select, _
    CopyToRange:=ws2.Cells(baslangic_satiri - 1, basliklar + 1), _
    Unique:=False
Next
ws2.Columns.EntireColumn.AutoFit
CMD6.Enabled = True
End Sub




Private Sub CMD2_Click()
Unload Me
End Sub




Private Sub CMD4_Click()
If LB2.Text = "" Then
MsgBox "Please make a selection"
End If
If LB2.ListIndex > -1 Then
LB1.AddItem LB2.Value
LB2.RemoveItem (LB2.ListIndex)
End If
End Sub




Private Sub CMD5_Click()
If LB1.Text = "" Then
MsgBox "Please make a selection"
End If
If LB1.ListIndex > -1 Then
LB2.AddItem LB1.Value
LB1.RemoveItem (LB1.ListIndex)
End If
End Sub




Private Sub CMD6_Click()
Sheets("Sheet2").Select
Unload Me
End Sub




Private Sub LB2_Click()




End Sub
Private Sub UserForm_Initialize()




LB1.AddItem (Sheet1.Range("a1"))
LB1.AddItem (Sheet1.Range("b1"))
LB1.AddItem (Sheet1.Range("c1"))
LB1.AddItem (Sheet1.Range("d1"))
LB1.AddItem (Sheet1.Range("e1"))
LB1.AddItem (Sheet1.Range("f1"))
LB1.AddItem (Sheet1.Range("g1"))
LB1.AddItem (Sheet1.Range("h1"))
LB1.AddItem (Sheet1.Range("i1"))
LB1.AddItem (Sheet1.Range("j1"))
LB1.AddItem (Sheet1.Range("k1"))
End Sub








Sub Rando()
Dim lc As Range
Set lc = Cells.Find("*", [a1], , , xlByColumns, xlPrevious)
    Dim UniqueList()    As String
    Dim X               As Long
    Dim Rng1            As Range
    Dim c               As Range
    Dim Unique          As Boolean
    Dim Y               As Long
    Dim lc              As Long
    Set Rng1 = Sheets("Sheet1").Range("Data(1)")
    Y = 1
    ReDim UniqueList(1 To Rng1.Rows.Count)
    For Each c In Rng1
        If Not c.Value = vbNullString Then
            Unique = True
            For X = 1 To Y
                If UniqueList(X) = c.Text Then
                    Unique = False
                End If
            Next
            If Unique Then
                Y = Y + 1
                Me.LB1.AddItem (c.Text)
                UniqueList(Y) = c.Text
            End If
        End If
    Next
End Sub

Re: Receiving error codes with using user form to rearrange and remove columns

***Note*** I am not using the sub named "Rando" I was trying to use some code so that it would by dynamic instead of listing the "LB1.AddItem (Sheet1.Range("a1"))" , b1, c1, etc code.... but also couldn't get that working...

Re: Receiving error codes with using user form to rearrange and remove columns

Ideally yes, I would want an option to filter the data but if no filters are selected, then it would move everything.

The filters would be
1.) - Duplicate text one column (so, just the same if you were to filter a table) --- i was thinking this would be a check box field
2.) - date range for the columns that only contain dates (there are never any columns that have a mix of dates and text) --- was thinking this would be 2 fields for the date range
3.) - Blank or not Blank

So, my original plan was to have

1.) list of all fields (tier 1) --- you would select which ones you wanted to move and then in "tier 2" of the report you would have 3 separate choices of where to add the item from tier 1. Those choices would be the 3 filtering options I listed above
-
2.) 3 separate areas to add those fields to, with 3 separate add/remove areas because of the 3 different filter options (this would be what i am referencing as tier 2)
- from there you would filter accordingly and again select "add" which would move the data to the final area prior to selecting "Go" or something where it creates the new sheet. (I would imagine all of the filtering could be done in the background as the user is making their choices.. then the "Go" button would just be moving all visible cells...

3.) tier 3 would be the final selection... which is the option to remove columns or rearrange the column order

I am not even sure if this is doable but it made sense to me because of like I said, the filtering as you go in the background..

Re: Receiving error codes with using user form to rearrange and remove columns

I guess the tier 3 would just be just moving all visible cells from 1 column, one at a time, in the order of the selection..

Re: Receiving error codes with using user form to rearrange and remove columns

just thought about it and I guess it would actually be 4 tiers... first tier would be a list of all of the columns (not sure if I mentioned this but it needs to be dynamic
in the sense the number of columns will vary.. and the date fields, etc. would also vary from column to column depending on the data in the spreadsheet...

Tier1 - All Columns (Individual Selections to choose how to filter)

Add / Remove Box Add/Remove Add/Remove
Tier2 - Select which filter items (duplicates) you want (Checkbox) / Date1 (Text Field) Date2 (Text Field) / Blank or Not Blank (Select which)

Tier 3 Add Add Add
Table would be filtered in the background at this point and the selections would be added here

Tier 4 Choose the order of the columns (Move up, down?)
Remove any columns you do not want

Send to Spreadsheet Select "Go" and create new worksheet with the
then clear original table filtered / arrange / visible cells data
filters

Re: Receiving error codes with using user form to rearrange and remove columns

Here is a better representation of it... I feel like it shouldnt be THAT difficult if you know what you're doing and have used these each on an individual basis ... its just that I haven't done all of these via userform before

Re: Receiving error codes with using user form to rearrange and remove columns

Guess you cant help.