ComboBox AutoFilter results to different sheet 4

[alwayslrN]

Hello, first let me start by saying am very new to VBA and I've searched countless sites, including this one, to get to what I need but I am not sure what to do at the same time as being overwhelmed.

I am using Excel 2000 on a Windows 2000 machine.

What I have is a worksheet currently just called sheet2 that holds source data. I have called that data training_database using the OFFSET function so the named range can grow dynamically.

On another sheet I hold records for three other named ranges, Group, Level, and Type. Each of these is also set up to grow dynamically.

On a third sheet, sheet6, I have three comboboxes, cmbGroup, cmbLevel, and cmbType. I populated these using their ListFillRange.

The database I mentioned above has the following headings. Group, Level, Type, Course, and Organization. What I want to do in the sheet that contains the comboboxes is select the records that correspond to the records for the value selected in the list, but on that page I only want to display the Organization and Course.

I recorded a macro using AutoFilter to see the results, but I am not sure how to tie this to what I need.

In the cmbGroup change event, for example, I put the following, but I know this is not going to do what I want, but I really do not know enough to do what I want to do.

Private Sub cmbGroup_Change()
Range("training_database").AutoFilter Field:=1, _ Criteria1:=cmbGroup.Text
End Sub

I really would appreciate some guideance. I've purchased several books and have looked online, but I am lost.

Thanks

 

[xlbo]

To answer this one:
(3) For that copy I need to be sure the macro code only copies the number of rows that result from the filter. I've found when I record macros it records the range that I select, but that range will be different each time. Always the same two columns, but the number of rows can be different.

You can select ALL rows to copy - only visible ones will be transferrred

eg:
lRow = range("training_database").rows.count
Range("A1:B" & lRow).copy


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Fenrirshowl]

xlbo

Thanks for the input but I am obviously doing something wrong. Cell A1 on sheet2 is named anchor, there is a 2 column table on sheet1.

I click A1 on Sheet1 and select Advanced filter. I select the copy to and the 3rd drop down becomes active. I enter anchor and I get "you can only copy filtered data to the active sheet". Using Sheet2A1 (i.e. no bang) returns "The text you entered is not a valid reference or defined name", same result using Sheet2anchor as the destination.

Fen

 

[xlbo]

Apologies - you must start the Advanced filter from the destination sheet so

1: Go to sheet 2
2: Data>Filter>Advanced Filter

Enter data into the range boxes as indicated

should work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DirkStruan]

Thanks xlbo

I've set that up and the initial go works fine, if you then amend the criteria, how do you get the advanced filter to automatically update - is this something you need to do with VBA and a change event? (Because at present changes in the criteria are not reflected in the filter results).

Cheers

D

 

[xlbo]

yeh - you will need some code in the change event to make it run

record yourself soing the advanced filter and then lob it into the change event and you should be fine

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DirkStruan]

Many thanks

D

(I thought your advice was worthy of a star!)

 

[alwayslrN]

Hi all - I have a question based on what I am seeing on the last bit of code I used and something D and Geoff suggested.

First, D suggested:
Use Application.ScreenUpdating = False at the start of the macro and then return it to True at the end - this should stop the flashing.

and

Geoff mentioned in an earlier post to turn the autofilter off.

Since I had to bypass some of the suggestions, mainly because I was so far into what I had already started and somewhat understood what I was doing, I ended up with the last set of code that I posted above. That being the case, using what I have so far, where would I put these tasks that D and Geoff mention?

Also, since choosing any one of the three combo boxes will change the filter I need to copy and paste each time those are changed, thus when would I do this turning off of the autofilter, on entry to the worksheet and exit of the worksheet. Because right now I soon as I open it, the thing starts running the copy and past and the filters are already on. That's annoying.

My other problem is using the macro that was created for the copy paste, if for some reason I have placed the cursor somewhere other than where I started the macro when doing the run, the copy paste does not work correctly.

Thanks

 

[DirkStruan]

Question 1:

Private Sub cmbGroup_Change()
Application.screenupdating = false
...
...
...
Application.Screenupdating = true
End sub

For all comboboxes.

What runs when you open it? (I assume when you open the spreadsheet).

Question 2

Sheets("Sheet6").Range("A4:B4").Select
Range(Selection, Selection.End(xlDown)).Select
etc

fixes all references to sheets and cells. Can you explain exactly what happened with the a different activecell when you start the macro?


Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[alwayslrN]

Thanks Dirk - I really appreciate your help. I guess one additional thing that would help is a brief explanation of what is being done, but I'll take what I can get.

As to your question, when I open the workbook the combo boxes have what ever values were there when I closed the workbook and for those values the filters and copy/paste processes run. I hope I explained that a little better this go round.

As to the second question, if I understand it correctly, I must have moved the cursor to a cell that I was not part of the copy/paste macro (remember I build this by taking and tweeking the macro produced from a recorded macro) and when I make a selection from the combo box and the copy/paste ran it seems like the starting place on the training_database page starts somewhere other than the first line for the Course and Organization columns (the columns that are being copied). Did that make sense or answer the question?

Thanks again

 

[DirkStruan]

Screenupdating true and false dictates whether you see what the macro is doing on your monitor. Say you had a macro that selects, in turn, lines 1 to 10 and pauses on each for 5 seconds. If screenupdating is set to true you would see each movement line by line, if it's false you would see that "a" macro was working but the selection would skip from wherever you were to row 10 after 50 seconds.

If you turn it off, the selection of various sheets and the copying of cells (which is quick and the source of the flickering) will not be seen.

Do you want to reset the comboboxes when you open the spreadsheet? You can set a macro to run when you open the workbook which can incorporate this (again turning off the screenupdating should make this almost imperceptible). You should be able to reset the comboboxes after all of your work, so the hint to the automatic macro on opening is:

Sub auto_open()
MsgBox "It works"
End Sub

Copy it, paste it to a general module, save and re-open to see it work.

Still not understanding the problem with the initial cell location as "Range("D1").Offset(1, 0)" effectively means start at cell D1 and go down 1 row and 0 columns across (i.e. D2). There doesn't appear to be a relative location in your code to be affected by a different starting position.

Going to call it a day now (it's about 9:50pm with a few things left to do today) but will pick it up again in about 10 hrs if no-one steps solves your problem beforehand.

Later

D

 

[alwayslrN]

Hi D -

I had a day full of meetings so I'm just now getting back here for today (3:47 pm EST).

As for the last comment, your advise in the previous email (Question 2) took care of the issue I was having so that offset thing is working correctly.

For the first question, I do not want to reset, I just do not want the procedures to run when the workbook opens. That is what is currently happening for some reason.

I am getting ready to test your hint now.

Another thing that is happening that I do not like is after the pasting is done, the area that was pasted remains selected. Is there a method to get rid of that?

Oh, and I've added some border formatting. Since one of those copy paste clear methods uses Range(Selection, Selection.End(xlDown)).Select when the filter yields no records I end up with borders all way down to the last row. I have not had a chance to play around with that yet to figure out how to stop that.

Thanks again for your help.

P

 

[DirkStruan]

The Autpen macro is the way to get things to run when you open a workbook - check to see if you have any sheet event macros set up (i.e. workbook opens, sheet is activated and it is the activation that triggers the macro).

If you don't like the pasted area being highlighted at the end of the macro, enter a line of code to select a different (single) cell. Choose the logical cell you would select after running the code.

If you want borders under certain circumstances only, you might want to take a look at Format menu\conditional formatting.... and add borders when the cell is non-empty.

Have fun

 

[alwayslrN]

It's 10:36pm EST and I have completed the filtering portion of the task.

I looked through the macros to see if anything was happening as far as the auto_open concept. I did get rid of some macros that were not needed that if I am not mistaken may have been running because the range object did not have an object before it, thus making it an Application object. Just a guess. I'm new to all this.

It was quite a learning experience. I am more than sure there was a better way to do what I did, but at least I can say, with what I have, the thing is working.

Thank You again everyone.