Posts by gijsmo

    This is clearly an extension of the original request.


    May be best to start a new thread and also include a sample spreadsheet with a mock-up of the final result you are trying to achieve.

    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

    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

    The dynamic range called Teachers becomes invalid when you delete cell C3 since it is tied to that cell.


    Maybe change the Worksheet_SelectionChange code as follows to reset that range when you delete in that column:

    Code
     'Move up teachers
      Application.EnableEvents = False
      Rng.Delete Shift:=xlUp
     'Reset the dynamic range
      ActiveWorkbook.Names("Teachers").RefersTo = "=OFFSET(Teachers!$C$3, 0, 0, COUNTA(Teachers!$C:$C))"
      Application.EnableEvents = True


    Also best to add the EnableEvents False/True code otherwise you'll trigger the Worksheet_Change event whenever you delete a cell.

    The way the code is currently written, it is only looking forward at any point in time ie, it is not looking at the whole set of data to determine the count for each row of data. This is why any countif code takes so long to run.


    Your code currently provides accurate results if the rows of matching data are sequential eg rows 1935 and 1936.

    This code would probably work as you require it if the data is sorted first, assuming data sorting does not interfere with anything else you may be doing.


    You will also speed up processing by adding the following at the start of the macro

    Code
      With Application
        .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False
      End With


    and the corresponding code at the end of the macro:


    Code
      With Application
        .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True
      End With

    I suspect COUNTIF is not providing the correct answers because you have a mixture of numbers and numbers stored as text.


    You should be able to work around this by forcing a formatted count. As an excel formula, this would look something like:

    Code
     =COUNTIF($A$2:$A$349397,TEXT($A12,"#"))


    One way to do this in VBA is something like:



    This loads the entire dataset to be counted into an array and then places the countif values into a second array.

    It may be more efficient than other types of loops however with nearly 350,000 rows this is still going to be very slow.

    Also, loading all the data into arrays first may be more efficient but it will be memory intensive.


    If you want to test it first maybe try using a smaller loop eg:

    Code
    For i = 2 to 100

    Excel has a habit of interpreting dates as mm dd yy (even if that is not your local date setting).

    You generally need to code around it, maybe try:


    When using an array with RemoveDuplicates, it needs to be a zero based integer array.


    In the example file you posted there are only 11 columns, so the code snippet for this example would be:



    Obviously, you can expand this if your actual workbook has more columns but the principal will be the same.