Posts by Malkier

    Thanks Carim - I have tested this and it works, so thank you! In trying to add some additional functionality it has stopped working.

    I have attached the sample file.

    I would like to be able to call one of two macros on worksheet change of a dropdown in cell $V$1 (I have named this range "ColumnChange"). I have attempted to do this using a Privatesub but now the original macros don't seem to be working themselves.

    It now is intended to run as follows:

    1. Look for change in cell value in Sheet5, based on this value run the appropriate macro (They are both identical, they just copy and paste a different range)
    2. Paste the copied data as in your macro below.

    Thanks in advance...

    Hi I am looking to alter code from Vcoolio the following post:

    Find string match in column then paste adjacent cell

    Here are the steps I am looking to execute:

    1. Look for change in cell value in Sheet5 cell $V$1 (there is a dropdown in this with two options) - at this stage I have attempted make this work via a Worksheet Change
    2. Look for first instance of the String "Test Column" in Column A.
    3. If value of $V$1 is "Big_Column" (without quotes), Select named range "Big_Column" expand the selection by two additional rows - then copy, OR if value of $V$1 is "Small_Column" (without quotes), Select named range "Small_Column" expand the selection by two additional rows - then copy
    4. Paste expanded selection 1 row down and 7 columns to the right of the 'found' string.
    5. Look for next instance of "Test Column" in Column A, then repeat steps 2 and 3, till all found, then end.

    Any help to get this code working would be much appreciated.


    Here is my edited code. I have only attempted to paste one of the ranges in this code but would like to do both. It could be done with two separate macros if this is cleaner, activated by the worksheet change macro.

    Re: Repeat "With" on different range not working

    Thanks so much Stephen for your continued assistance.

    It seems you have cleared up the "overwrite data" warning popping up unecessarily but it seems the 'For' loop is now looping through the PasteRange as intended, but returns the Else MsgBox "Plant Name not found. Please enter new plant name and region on the 'Base Data' sheet."

    I know this as "Plant Name not found. Please enter new plant name and region on the 'Base Data' sheet." pops up 41 times = Offset column value for Table1
    and "Plant Name not found. Please enter new plant name and region on the 'Contacts' sheet." pops up 13 times = Offset column value for Table2.

    Anyway - with a little bit of juggling of the 'Exit For' and making the 'If Cell.Value <> "" Then' to 'If Cell.Value = "" Then' and putting the copy-paste straight after and doing the Else for the <> case with the popup it all worked fine.

    Thanks for your help!

    Re: Repeat &quot;With&quot; on different range not working

    Stephen - you were right to be confused!

    That is what I meant to do. Having edited the code as above I get the "Overwrite data" message for every cell that has data in it. What would be great would be if i could modify it to trigger when it finds the first cell with data, give the warning message and then exit after that.

    What I can't work out either is why the Else at the bottom did not work as required when I deleted all the data, see notes inline below:

    Re: Repeat &quot;With&quot; on different range not working

    Stephen - I managed to get the second block to work. The code relies on the user having entered a plant name and I had deleted it in testing...

    Having said that it highlights that the code I have to pop up a message when no plant name is entered is not working, nor is it properly detecting non-blank cells. Even when there is no data it gives the message box "Plant/organisation already has contact data entered. Overwrite and update this data?".

    Any ideas?!

    Thanks for your help...

    Re: Repeat &quot;With&quot; on different range not working

    Quote from StephenR;701650

    Is it PasteRange or Pasterange2?

    PasteRange works ok for the first block - PasteRange2 is the in the second block that does not work. Incidentally I have cut the second block out into it's own module to try and see if the problem was following behind the first block but it still does not work.

    Re: Repeat &quot;With&quot; on different range not working

    Quote from StephenR;701647

    What does "not so good" mean?

    One thing I notice is that you are using the same variable in both blocks - Rng. If it is found in the first block, but not the second, it will behave as if it has been found because you are not resetting it to Nothing.

    Not so good means that the cells do not copy at all for the second range. Is the Rng variable causing the trouble?


    I am trying to write one of my first macros. I have successfully written a macro to copy one horizontal range from one sheet to a structured table on another when a match is found in a specific column of the strucutred table. I am trying to then repeat the process, copying a different horizontal range (on the same sheet as the first) to a different sheet also containing a structured table. The code is the same for each step - only the ranges are different.

    The first part of the code fires fine - the second not so good.

    The other issue I am having is that I have a message box set to fire when data in the target cells may be overwritten - but it is popping up even when the target cells are blank.

    Here is a link to the full problem in another forum where I am not getting much help -…ther-sheets-on-match.html

    Here is the code with the problems identified.

    Any help is much appreciated...

    Re: Have data from Multiple sheets automatically go to a master sheet

    Hi Just trying to make this code below work - do I just have to post it in the Class Module?

    My case is very similar but not exactly the same - see attached example. Thanks...


    I have two excel files:

    -Workbook1: Rows of data on Monthly sheets, currently................. I want: to dynamically display data from all sheets in a 'master sheet' in the same workbook (ie the master always shows up-to-date data).
    -Workbook2: Has formula that will search the master sheet in the above workbook and return all rows that match a set criteria.

    Question I would like solved is: How can I append data from the sheets in Workbook1 into one sheet? All data will have the same column headings but each sheet may have anywhere from 0-150, or more, rows of data. Ideally I would like not to have to open workbook1, to allow the master to update, for the formula in workbook2 to return a full and complete list.

    I would prefer a formula (that I can understand and perhaps use again) rather than VBA code but if someone can meet the above requirements with VBA then I might be swayed.

    Thanks in advance...