VB formula help

  • Hi VB Experts,


    I have the below code working well however what I have in my column F on my main tab is different months, what happens is when column F contains as an example January it would then copy that whole line to another sheet. Column F will contain multiple months for example "January / May / September", what i would like to do is have it move when say the cell contains "January" in the string rather than needing the whole "January / May / September" as sometimes the months are in a different order. It would seem like it currently only moves it to another sheet when it is an exact match rather than containing "January"


    Any help would be amazing and thanks in advance.


  • Go to Best Answer
  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • Hi Gijsmo,


    With the code changes made unfortunately nothing pulls across to the other sheets.


  • Wow so simple thanks so much works perfectly, what would be the simplest way Gijsmo to clear out each sheet before inserting the copied cells except for the top line?

  • There's a bunch of ways to do this. One way is in the attached revised copy of your original workbook.

    I made a few other changes as well to streamline the code a bit.

    I also added a Clear Data button to the Main sheet if you want the option to do this in one go - as the name implies this will clear the data from all the "Month" sheets (except the header row).


    Customer Call Plan 18-4-22 v2.xlsm

  • Hi Gijsmo,


    Wow thanks so much getting closer now, i have added the extra copy information for the other months through to December and noticed it only goes as far as April. It will continue to copy the other months but only if i put for example "May" in column F on the companies tab, putting "September / January / May" will result in nothing been copied to May or September sheets yet it pulls through to the January tab without any issues. Is there a limitation on how much can be done at a time with the ELSEIF?


    I have attached the edited workbook, thanks again for your help I'm very grateful for your skill.

  • The loop in the NewCopy macro that has the "Like" statements in it is created as a series of "ElseIf"s.

    This means that, for example, a Call Month value of "December / April / August" will only copy to April because April gets tested before August or December.


    If you want to copy data for a Call Month value of "December / April / August" to each of the December, April & August sheets then the loop needs to be redesigned. The attached version does this. Bear in mind that this loop now takes a longer to run and it has to test each row 12 times for each month, not skip to the next row like it did before when one of the ElseIf conditions was met.


    Customer Call Plan 18-4-22 v3.xlsm

  • The most efficient way is to forget splitting the data and simply use AutoFilter to view the required month.


    Rather than using Loops to check the dates AutoFilter could be automated with VBA to split the data. If the data is likely to change the the code could clear the destination sheet then split the data. Again the most efficient way.

  • Hi Guys, Wow this is amazing and much fast thanks so much for your help.


    What I am trying to do now is create a total on my main page that totals up the customers in each month be state. For look at the Sheet name, then the state and then report the total back on the main page. What would your thoughts be about how to go about this, I've played around with a few things, ideally this would report this back into a cell.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!