• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to working with correct sheet?

Dutchman

New Member
Good afternoon (in the Netherlands) I am new to this forum and I think it would be nice to ask my question on this forum (it is also good for my English language).
This is my question
I made a booking form for my son in Mexico to book studios I book them in with a usserform but from there I would like to divide them per studio.
Have now done it with the option = IF but then it is very messy per studio. (I work with Excel 2007 so I can't make a Tabelslicer). Anyone have another option.
Best regards, Ed
 

Attachments

  • Test Sheet.xlsm
    160.4 KB · Views: 5
Dutchman
As a new member, You should just read Forum Rules
Could You modify Your Title of thread as written in Forum Rules ?
How to get the Best Results at Chandoo.org
  • Use Relevant words in the Title and in the tag Box, This will aid future searches.
 
In the attached I've left the studio sheets all blank, for demonstration purposes.
There's a short 3 line macro in Module 3 which updates the 5 studio sheets. This macro is currently called when you leave sheet Data to move to another sheet.
It uses Advanced filter which should be OK for Excel 2007.
On each of the studio sheets it uses the headers on row 4 to decide which columns of data to retrieve from the Data sheet (for example, I missed out the Studio number column because that's what the sheet is about) as well as a criteria range at cells P1: P2 of each sheet.
The macro can be called any time and I've put a commented-out line of code in your Private Sub CommandButton1_Click() macro which should update all the sheets when you dismiss the userform. (I haven't tested this. You need to activate that line by removing the apostrophe at the start of it.)
 

Attachments

  • Chandoo45431Test Sheet.xlsm
    117.6 KB · Views: 4
Dear p45cal,
It works beautifully in the example sheet, I just don't get the fiters transferred to the original booking sheet. Would you please take a look at it again, the original is now included with the formulas from the test sheet
 

Attachments

  • Booking Casa Loto 4 orgineel.xlsm
    214.1 KB · Views: 1
The headers in the sheets need to be the same as in the Data sheet. (Those that you choose to use, that is.)
I've done something a little different in Studio1 sheet; hidden row 4 and left your headers in row 3.
Instead, the little macro could temporarily change the headers, do the filtering, then change them back.
 

Attachments

  • Chandoo45431Booking Casa Loto 4 orgineel.xlsm
    181.1 KB · Views: 5
Back
Top