Posts by gijsmo

    Maybe change the FOR loop - you could check for a "Y" value in col Q (or modify the code to see if it's not an "N" value)


    From:

    Code
        For Each cell In myDataRng
            If Trim(sEmail_ids) = "" Then
                sEmail_ids = cell.Offset(0, 0).Value        ' keep the Offset (0, 0)
            Else
                sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value
            End If
        Next cell

    To:

    Code
        For Each cell In myDataRng
          If UCase(cell.Offset(0, 2)) = "Y" Then   'check if there is a "Y" in column Q
            If Trim(sEmail_ids) = "" Then
                sEmail_ids = cell.Offset(0, 0).Value        ' keep the Offset (0, 0)
            Else
                sEmail_ids = sEmail_ids & vbCrLf & ";" & cell.Offset(0, 0).Value
            End If
          End If
        Next cell

    Maybe try this instead:


    In that case, maybe need to change


    From:

    Code
    ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(1, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1

    To:

    Code
    ThisWorkbook.Sheets("Bestelde Artikelen").Range("A" & Rows.Count).End(xlUp).Offset(2, 8) = Sheets("Bestelde Artikelen").Range("M2").Value + 1

    Maybe try this:


    Assuming Red is the value you want to display if the condition for Green & Amber are not met, this might be all you need:


    =IF(Q5>Data!L10,"Green",IF(ABS(Q5-Data!$L$10)<=0.5,"Amber", "Red"))

    Maybe try this:


    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.