VBA - Copy/Paste last value in column if the next cell is empty

  • HI


    I need to copy the last value in the column every month and copy paste it into another document which shows the KPI for the current month. So if you look up the picture i need all the values from mar2021 to be copied this month and apr2021 next month.


    Is it possible to make some kind of VBA code for copy and paste last column in row if the value beside it is empty (like in apr2021).

  • Hi,


    What you are requesting is indeed possible ...:)


    But, an image is a totally inert object .... please attach a sample file ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Great!


    Here you go. I need to copy the data from mar2021 (in data.xlsx) into the MonthKPI workbook and next month apr2021 into MonthKPI. So activate the macro every month, too transfer the newest data.


    Thx.

  • Hello,


    Is there a specific constraint for you to have separate workbooks ... ?


    Things would be easier for your with Ark2 ... always showing " Current Month " ... without any macro ....;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • This is just a simulation of the real problem. I need to do this from workbook to workbook because there are over 10 people inserting KPIs (shared workbooks).


    I the original workbook are there probably 200-300 row with KPIs where 20 of them is mine.

  • Something like this maybe..



  • OK ...


    A couple of questions about your actual real-life situation:


    1. Are you working with opened or closed workbooks ...? ( methods do differ a lot ...)


    2. Do you already use a macro to open your "central" workbook ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Our last two messages crossed ...


    Just saw your Test macro ...


    What is wrong with this macro ...?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Its okay using macro to open the two workbooks and no im not using a macro to open. My thought was to run the macro within MonthKPI workbook.


    Haven't tried the macro it was a something I found in on MrExcel.com

  • The choice of where to place the macro is yours ...


    If you decide to have it in the Data workbook ... you would be exporting to the Month KPI workbook ...

    and

    If you decide to have it in the Month KPI workbook ... you would be importing from the Data workbook ...


    Guess the only question is what is the most convenient for all your users ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I want to run the macro from Month KPI workbook and importing data from the Data Workbook :)


    Fine ...


    So the macro you posted will perform exactly what you are requesting ...


    May be the only instruction which is missing would be to save your Month KPI workbook: ws2.Save


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Hello again,


    You could test following

    Code
    1. Sub CopySomeCells()
    2. wbB.Sheet1.Range("E2,E4,E9,E10,E12").Value = wbA.Sheet1.Range("E2,E4,E9,E10,E12").Value
    3. End Sub

    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)