VBA to imprt data based on moving date range......

  • I have an example spreadsheet that contains data across a date range against a list of names on sheet one


    I have used index/match to import the cell contents to sheet 2 but I also need to show any comments and cell colours.


    This isn't possible using index match but I believe it is with VBA. I would like it to work when the data updates....so the formatting changes corresponding to the data.


    Example attached


    Many thanks


    Simon


    [ATTACH]n1204148[/ATTACH]

  • Copy and paste this macro into the worksheet code module. Do the following: right click the tab for Sheet2 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter the date in cell B4 and exit the cell.

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Intersect(Target, Range("B4")) Is Nothing Then Exit Sub
        Dim strdate As String
        strdate = Target
        Dim foundDate As Range
        Set foundDate = Sheets("Sheet1").Rows(2).Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
        If Not foundDate Is Nothing Then
            Sheets("Sheet1").Range("B3").Resize(9, 7).Copy Range("B5")
        End If
    End Sub

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you for that it works ........but not as I need it to.


    When I change the date in B4 the data in B5:H13 is not updated....it remains the same.


    There is a formula in B5:H13 that imports the cell contents(only the 1st 2 characters) from sheet1 when the date in B4 is changed or be blank when B4 is blank.......and I want that to remain....unless it can be built into the code. I only need to add the Comments and Cell Colours using the VBA code.


    Many Thanks in advance...

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thank you


    Its getting there. The contents of the cells up date when the date is changed as required but the Comments and Colours stay.


    I need the Comments and Colours to behave in the same way as the contents. Move with the date they appear under.


    If the Date in B4 is deleted then the range goes blank.


    Many thanks

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Ok it is still not moving the Comments or Colours.


    If you enter a date it will populate as on sheet 1, contents,Comments and Colour, correctly. When the date is changed the contents changes but the Comments and Colours remain from the previous date. The comments and Colours need to move with the dates....


    Sadly I don't have any VBA knowledge so I cannot see how your suggestions operate.


    Many Thanks once again!

  • What date are you changing it to?

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Any of the dates shown on sheet 1.


    I start with 1/04/2018 and it populates with contents, comments and Colours.


    I then change to 05/04/2018 and it will update contents, as per formula, but Comments and Colours remain as they were from previous date.


    If I then delete the date all contents disappears but Comments and colours remain.


    *****


    What ever date I enter, that appears on Sheet 1, the Comments and Colours are the same as though I had entered 1/04/2018.


    So it is only picking up the comments and colours for that date.


    Thanks

  • Try:

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Ok this time it will format correctly for the inputted date. So any date from sheet 1 can be entered in Sheet 2 B4 for the 1st time and it will format as desired


    But entering subsequent dates the Comments and Colours remain from the first input date.


    I still need the formatting (Comments and Colours only) to change with the date on sheet 2 to match sheet 1.


    Many thanks


    Simon

  • The macro works properly for me on the file you attached in your original post. Are you using the macro on the same file or on a different file? If it's a different file, please attach a copy.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • OK I have used it on the attached example and another I have......neither work for me!!???


    I am doing as you said in your first reply but it only gives the first lot of formatting and doesn't change or clear....am I doing something wrong?

  • The attached file is working for me. Enter the date in yyyy/mm/dd format in cell B4.

    Files

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I will try that when I am back in the office. I now have 2 weeks off work so it will be after that.


    A couple of questions...
    Why does the date format have to be written in reverse? People will use the Excel shortcut to input today date for example.....
    Can it be made to work with dd/mm/yyyy?


    and


    Given that this is an example and with the expectation that it will work, I will explain how I need it to work for the final spreadsheet I need to create.


    It will do exactly the same as the example but will collect the data from 2 other workbooks. Both are staff rotas.


    In Workbook 1 there is a list f 14 names in C4:C18. In Row D3:NJ3 are the dates for a year from 1st April to 31st March
    In Workbook 2 the is a list of about 60 names C4:C56. Dates are as above.


    From workbook 1 I need to collect the data/comments/colours from the last 7 names C11:C18
    From Workbook 2 I need to collect the data/comments/colours from two lots of names form C11:C18 and C40:C47


    In work book 3 I will have a list of names similar to the example with one cell to input the date and 6 other cells with the date derived from B5.
    The names will be in Column C but the rows will not align with the other workbooks.


    Needless to say I have to formula to collect and display the data but I need a code to import the matching comments/colours and for it all to change with date changes and then to clear when the date B4 is blank


    I hope this is possible???

  • A suggestion for future posts: It is better that you post your question based on the actual workbooks and data right form the start. What works with sample data most often will not work with the actual data which means that we have to start all over again. In your next post, attach copies of all original files with a detailed explanation of what you want to do referring to specific cells, rows, columns and worksheets.

    You can say "THANK YOU" for help received by clicking the "Like" icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Unfortunately I am not able to upload the full spreadsheets as that is not allowed by my employer.


    All I was allowed to do was to provide an example and then adapt that to work for me.


    The main difference is that I need to import the information into one workbook from 2 others and not from one sheet to another. I can do it using the index/match formula. I got that working on the example and then adapted as required. I was hoping to be able to do the same with VBA code.


    I will create 2 other workbooks as an example for you when I return to work.


    I do greatly appreciate all the work you have done and it is a great help for me to see the programming required to answer my question.


    I will create another post in due course!!


    Many thanks thus far!


    Simon

  • Unfortunately I am not able to upload the full spreadsheets as that is not allowed by my employer.


    All I was allowed to do was to provide an example and then adapt that to work for me.


    The main difference is that I need to import the information into one workbook from 2 others and not from one sheet to another. I can do it using the index/match formula. I got that working on the example and then adapted as required. I was hoping to be able to do the same with VBA code.


    I will create 2 other workbooks as an example for you when I return to work.


    I do greatly appreciate all the work you have done and it is a great help for me to see the programming required to answer my question.


    I will create another post in due course!!


    Many thanks thus far!


    Simon

Participate now!

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