Posts by vcoolio

    Hello Roy,


    Hope you're well and safe over there in the UK.

    Quote

    I don't understand the Case 1 line though

    I didn't actually read through Steve's code but that's an interesting query.

    Having now actually read the code, I'd assume that the second part of Case "0" isn't needed as well as Row9 is already hidden.

    I'll be interested to learn of Steve's reason.


    Cheerio,

    vcoolio.

    Hello Steve,


    More like this (I haven't tested it though)...........



    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Steve,


    The Worksheet_Change event code needs a User to physically (for want of a better word) make a change to the cell(s) in question so

    try using the Worksheet_Calculate event code instead:-


    Code
    1. Private Sub Worksheet_Calculate()
    2. Your code here
    3. End Sub

    You won't need to use "Target as Range" but instead reference the target cell directly.

    Here are some OzGrid tips:-


    https://www.ozgrid.com/Excel/f…BA1/excelvba1lesson16.htm


    I hope that this helps.


    Cheerio,

    vcoolio.

    Quote

    Hi vcoolio . Thanks for sending your solution through. The only thing it wasn't doing was clearing the report content at the start of each run - but I've amended that and it now works perfectly so thank you.Hello Maid1812,

    You're welcome. I was wondering whether or not you wanted the data cleared after I had posted but I'm glad that you were able to sort that out for yourself.

    I'm glad to have been able to assist and thanks for the feed-back.


    Cheerio,

    vcoolio.

    Hello Maid1812,


    I don't see any problem with the code Mumps has supplied but you have placed his code in the ThisWorbook module. It is not an event code and needs to be placed in a standard module and assigned to a button. This could be part of the reason why its not quite working for you.


    In the meantime, here's another option:-



    I've attached your sample workbook with the code implemented and assigned to the
    "TRY ME" button. I've also coloured the font in the"Completed" sheet red just so you can more easily see from which source sheet the data has come from.

    Click on the "TRY ME" button to see the code at work


    I hope that this helps.


    Cheerio,

    vcoolio.


    Maid1812.xlsm

    Hello Chezziih,


    It would probably be a better option to prevent the mistake from happening before any transfer takes place with a message box popping up asking if the correct value has been selected from the drop down. Here's the code amended allowing for this:-

    Let us know what you think.


    BTW, please ensure that the spelling of the sheet names matches exactly with the spelling of the sheet names in the drop downs; e.g. Finances/Finance. The code will error otherwise.


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Chezziih,


    Try the following code placed in the worksheet module:-


    To implement this code:-


    - Right click on the "All" sheet tab.

    - Select "View Code" from the menu that appears.

    - In the big white code field that then appears, paste the above code.


    When you select a criteria from the drop down selections, the relevant row of data will be immediately transferred to the relevant sheet.


    Quote

    Is there also a way to remove it if the user changes selection in the dropdown on the first sheet.

    Could you please clarify your intent.


    Cheerio,

    vcoolio.

    Quote

    This is great! all working now thank you for your help!!

    You're welcome. I'm glad to have been able to assist.


    Cheerio,

    vcoolio.

    Hello Joe,


    See if this works for you (untested):-


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello people,


    Just backing Mumps up here. I have the same problems. Its no big deal but it just looks untidy. I'm assuming that if an OP copies/pastes a supplied code, it will just be all lined up, line after line beneath the next with all spacings and indentations gone.


    I tested this myself in a thread I've replied to in the VBA/Macros forum by writing the code in a Module, copy/pasting it to the thread, deleting it from the module and then copy/pasting it back to the module from the thread. The result is as Mumps has described.


    Just my two cents' worth.


    Cheerio,

    vcoolio.

    Hello DW,


    One way to prevent the duplication is to delete the rows of data that have been transferred over to the relevant sheet. But I'm assuming that you don't want any of the data from the Lead Tracker sheet to be deleted. Hence, refreshing the destination sheets should work for you. Replace the "Test" code in the standard module with this amended code:-

    and in the Lead Tracker sheet module, place this code:-


    Thus, every time you make a Buy or Sell selection from the drop downs in Column G, the relevant row of data (Columns A:E) will be transferred to its relevant sheet without duplication. You won't need a button for this.

    To implement the Worksheet_Change code:-

    - Right click on the Lead Tracker sheet tab.

    - Select "View Code" from the menu that appears.

    - In the big white code field that then appears, paste the code.


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello DW,


    See if the following helps:-


    I've used the sheet codes instead of the sheet names in the code above.


    Please note that this code is not an event code and needs to be placed in a standard module the assigned to a button) and not the sheet module (part of the reason why your code doesn't work correctly).


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello DKool,


    Here's another option:-


    I've attached your sample workbook with the code implemented. Click on the "RUN" button to see it work. You'll note that I've placed a heading name in Column A of Sheet1.


    I hope that this helps.


    Cheerio,

    vcoolio.

    DKool.xlsm

    Hello Maurice,


    I would say that that error has come up because the listbox is on a different sheet.

    Hence, in front of this line:-


    ListBox1.AddItem c.Value


    place the sheet name in which the listbox resides.


    e.g.:


    Sheets("HONDA SHEET").ListBox1.AddItem c.Value


    You can, of course, declare a sheet variable to tidy things up a little just like in the first code above.


    Cheerio,

    vcoolio.

    Hello Maurice,


    From what I can understand, you just want the sorted cell range from the SORT sheet loaded into the ListBox.


    If that's the case, then you could do it with two codes as follows:-

    The LoadListBox sub is called through the NEWSHEET_Click sub. The SORT sheet is then deleted as well.


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Maurice,


    Try your code amended as follows:-


    I hope that this helps.


    Cheerio,

    vcoolio.

    Hello Meetmoras,


    Where are you going with this line:-

    Code
    1. ActiveCell.Offset(-2, 0).Select


    If "abc" is in Cell A2, the line of code will try to find an offset one row above the headings row (Row1). Of course, this will generate an error.


    As the criteria "abc" is in Column A only, using autofilter may be a better option for you, so try the following:-




    It will delete all rows with the criteria "abc" in Column A of all sheets.


    I hope that this helps.


    Cheerio,
    vcoolio.