VBA script to copy data based on Cell criteria

  • How can I write a VBA script to copy data from a column and paste it into another worksheet based on a criteria in a cell?


    Example: Assuming the table below = RowsA6 : F6. Cell C1 = the current month.
    I would like to copy from another worksheet, which looks like the one below, actual data for only column B (Apr). Monthly, I would change Cell C1 to read the current month.
    And of course copy only data for that current month. So, in May, I want only need to update column C, with Actual for May and so on and so forth.



    [TABLE="border: 1, cellpadding: 1, width: 500"]

    [tr]


    [td][/td]


    [td][/td]


    [td]

    Apr

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    Description

    [/td]


    [td]

    Apr

    [/td]


    [td]

    May

    [/td]


    [td]

    Jun

    [/td]


    [td]

    Jul

    [/td]


    [td]

    Aug

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]

  • It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Click the paperclip icon in the upper right of the menu to the left of the "A" in your response.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Just a few questions for clarification:
    - Are columns B, C and D in the "Update Cur" sheet the values for JEEP, BARN & TRACK respectively?
    - Each of the 3 sheets JEEP, BARN & TRACK currently has values for each month. Do you want the values from the [SIZE=13px]"[/SIZE][SIZE=13px]Update Cur" sheet to replace the values in the other 3 sheets or to be added to the values that are currently there?[/SIZE]

    [SIZE=13px]-Does the data in the [/SIZE][SIZE=13px]"[/SIZE][SIZE=13px]Update Cur" sheet actually start with the headers in row 5 and the other 3 sheets with the headers in row 4?[/SIZE]

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Just a few questions for clarification:
    - Are columns B, C and D in the "Update Cur" sheet the values for JEEP, BARN & TRACK respectively? Yes, the values are for each sheet respectively.
    - Each of the 3 sheets JEEP, BARN & TRACK currently has values for each month. Do you want the values from the "Update Cur" sheet to replace the values in the other 3 sheets or to be added to the values that are currently there? The values in Column C "Apr", should be replaced with the values from the "Update Cur" sheet. Monthly I will change Cell "I2" to show the current month to be updated.
    -Does the data in the "Update Cur" sheet actually start with the headers in row 5 and the other 3 sheets with the headers in row 4? The header on the "Update Cur" sheet should be in R4, sorry about that.


    THANKS MUCH FOR HELPING!

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • This is absolutely brilliant! It works perfectly. I even added another sheet adjusted the code and it was brilliant. Thanks a million times.


    Try:

  • Hi [USER="138669"]Mumps[/USER], I crave your assistance with another macro, on this same work file. I will now have another workbook, named forecast, which should update these sheets but only for the other months. E.g. If actual is April, the forecast should update from May to March. Should I send the forecast file.

  • Please post a copy of the file and explain in detail what you want to do referring to specific cells, rows, columns and sheets using examples from your data.

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Thanks.
    Each month the Actual will be updated from the "Update Cur" sheet in the 'P&L Actual-Forecast' workbook. Likewise, the other forecasted months should be updated from individual workbooks.
    example:
    April is update from the first macro to show the actual information
    May to Mar should be updated from the individual forecast files


    In May
    May is update from the first macro to show the actual information
    June to Mar should be updated from the individual forecast files


    In June
    June is update from the first macro to show the actual information
    July to Mar should be updated from the individual forecast files


    In July
    July is update from the first macro to show the actual information
    August to Mar should be updated from the individual forecast files


    In August
    August is update from the first macro to show the actual information
    September to Mar should be updated from the individual forecast files


    In September
    September is update from the first macro to show the actual information
    October to Mar should be updated from the individual forecast files


    and so on until Mar is update with Actual information from the "Update Cur" sheet.


    Next year the cycle continues.


    The other two attachments will follow due to the limit on attachments.

  • Will the

    individual forecast workbooks always be open or does the macro have to open them?

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • In sheet "Update Cur" in your P&amp workbook, move all your data up so it starts on row 4 instead of row 5. Place the macro in the [SIZE=13px]P&amp workbook and with all the workbooks open, run the macro.[/SIZE]

    You can say "THANK YOU" for help received by clicking the :thumbup: 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.

  • Thanks, it works well. However, I want the actions separated. I want to keep the first Macro you gave me and have this new one only to update the forecast. I will have separate buttons to reflect both Macros.


    How do I modify to have them separated?

  • Try:


    You can say "THANK YOU" for help received by clicking the :thumbup: 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.