Using Offset?

[Guest]

I have a table of data, with column headers.
I've got a userform (my first one!) and users first select which column
headers, then select which data within those headers
(EG. They select "Users" and "Date Worked" and then select "Tom" and "Frank"
from the Users and "13/10/06" and "10/10/06" from Date Worked)
When the Finish button is pressed i've already got it creating a new
worksheet and naming it "Results" then putting the column headers in the
order depicted by the user.

I'm at a loss how to copy the rest of the information over though.

Users Date Worked
Tom 10/10/06
Tom 13/10/06
Frank 10/10/06

So that all the information that relates to the selected data is copied. And
obviously if a row of data with "Tom" as user and "10/10/06" as Date worked
wasn't present, this wouldn't be included.

Listbox4 -> Listbox8 include the column headers (these listboxes can only
have 1 or 0 entries)
Listbox11 -> Listbox15 include all the unique references in the columns
corresponding to listbox 4 -> 8. And these boxes allow multiple selection (so
you are able to choose "Tom" and "Frank" at the same time.
Listbox2 includes a list of the column headers as well, and Lisbox3 contains
everything from Listbox2 minus anything that has been moved to Listbox4 ->
Listbox8. The headers from Listbox3 that arn't moved should include all
information (since the headers are moved to allow selection of specific
items, anything left should contain everything)

I'm not expecting someone to write the entire thing, but a pointer in how to
get this done would be great.

Cheers

 

[Guest]

There is a lot of information about your list boxes, but very little about
the worksheets where the data to be copied resides. List boxex can be
initialized with code with AddItem or from a worksheet range used as a data
source. We could assume yours is loaded from a worksheet range, but would
have to guess about which range. We would also have to guess about where to
copy the data from and where to paste it. You need to clarify your
requirements and keep it simple enough that even I can understand.

 

[acampbell012]

Paul,

It sounds like you are bit too far along to turn back on this but, my
suggestion would have been to use the advance filter functionality,
filtering and copying the data based on user selection. Then, on the
results sheet, just delete the columns with heading not fitting the
user selection.

You could store the selection results and build a loop that goes
through your main sheet row by row, copying rows/cells matching your
criteria over to the results sheet.

More information and a sample of what your main sheet looks like might
help.

Alan

 

[Guest]

Its all from 1 worksheet.
The top row contains column headers.
Listbox1 is automatically populated by everything in row "1" on the
spreadsheet unless its blank.
Commandbutton1 moves the selected header from Listbox1 to listbox2.

This results in which headers are to be used.

Start Time End Time User Date Work Completed
9am 9pm Tom 10/11/06 116
9am 4pm Frank 13/11/06 96
10am 2pm Tom 13/11/06 74
9am 3pm Sam 10/11/06 94

So listbox1 will have
Start Time, End Time, User, Date, Work Completed
And the user has selected (and using commandbutton1 moved them to listbox2)
"User" and "Date"

The user has also selected "User" to be first, followed by "Date"

On the page containing listboxes 11 to 15 would look like

Tom 10/11/06
Frank 13/11/06
Sam

"Tom" and "Frank" have been selected from the first box, and both "10/11/06"
and "13/11/06" from the second box.

When "Completed" is pressed (a command button) a new sheet is added and
renamed to "Results" and the follow data should be on that sheet

User Date
Tom 10/11/06
Tom 13/11/06
Frank 13/11/06

So, the user selects which headers to use (Populated from row 1) and which
unique references are to be included from those columns.

Hopefully this is a tad clearer.

 

[Guest]

Paul,

it is difficult to understand your requirements. Are you wanting to use the
listbox selections to filter records in a table stored in a separate
worksheet (this is my best guess!)?

Maybe best to give an example giving exact details of:
- Listbox selections
- Data stored in the main data sheet (if there is one), and where this
sheet/range is
- Output required, including sheet/range

It sounds like this should be straightforward to solve...we just need more
details.

Cheers,
Dave

 

[Guest]

Hopefully my follow up will provide a better explaination. Just incase not...

The company produces several reports on a daily basis, for an example, this
might include all files closed during the previous day. This table of data
includes headers and underneath a list of all the files.
The first header could be "Reference Number" and all the records will have a
unique "Reference Number" the second column could be headed "Operator" and
show next to each reference number who was working on this file.
Other headers could include the date the file was opened, or when it was
closed. If the file was closed as a success or failure, etc.

A teamleader may wish to see information on a particular operator, and find
out how productive they are.

The idea of the userform, is to allow someone to select say the "Operator"
and "Result" columns, and only have it show files with "Tom" as the operator.

Essentially, the end result will look pretty much like the original data,
but with the data filtered to only show "Tom" files, the extra columns
deleted, the the columns kept reordered. Also, the idea is that this form
would be easier to use than filtering, deleting, copying and moving data
individual. This is mostly so it could be used by people who know little
about excel (the kind of people that ask me on a weekly basis how to use the
SUM() function correctly)

 

[Guest]

For some reason I never thought of the advanced filter, will have to give it
a look, seems a better option than trying to copy and paste things one by one.

The only trouble I forsee is that if someone wants more than 2 things, but
can also copy and paste one at a time I suppose.

As far as i'm concerned, when i'm making something its never too late to go
back and make it better and more efficient. Cheers for the suggestion.

 

[Guest]

Paul,

Yes, I see what is going on now!

This demo should work OK for you. it uses the built in Advanced Filter
function to do all the hard work. The only tricky bit is assembling the
criteria range required, with the field names in the first row and all filter
values below. I've created an extra worksheet for this (deleted at the end),
although you could use an existing worksheet.

Note that the Advanced filter will also accept operators >,< etc. if this is
useful later.

Sub Demo_AdvancedFilter()
Dim rngData As Range
Dim wsCriteria As Worksheet
Dim i As Integer, iLast As Integer, iRow As Integer

Set rngData = Sheets("Data").UsedRange

Application.ScreenUpdating = False 'hide sheet creation from user
'create criteria
Set wsCriteria = Worksheets.Add
'get field headings
wsCriteria.Range("A1").Formula = UserForm1.ListBox1.List(0)
wsCriteria.Range("B1").Formula = UserForm1.ListBox1.List(1)
'get filter items for first field
iRow = 2
iLast = UserForm1.ListBox11.ListCount - 1
For i = 0 To iLast
If UserForm1.ListBox11.Selected(i) Then
'add this item to the filter criteria
wsCriteria.Cells(iRow, 1).Formula = UserForm1.ListBox11.List(i)
iRow = iRow + 1
End If
Next i
'get filter items for second field
iRow = 2
iLast = UserForm1.ListBox12.ListCount - 1
For i = 0 To iLast
If UserForm1.ListBox12.Selected(i) Then
'add this item to the filter criteria
wsCriteria.Cells(iRow, 1).Formula = UserForm1.ListBox12.List(i)
iRow = iRow + 1
End If
Next i

'now apply Advanced filter to copy relevant rows
rngData.AdvancedFilter Action:=xlFilterCopy,
criteriarange:=wsCriteria.UsedRange,
copytorange:=Sheets("Results").Range("A1"), unique:=False

'delete criteria sheet
Application.DisplayAlerts = False
wsCriteria.Delete
Application.DisplayAlerts = True
End Sub

Cheers,
Dave