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

[Mortenhoey]

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).

Picture

 

[Mortenhoey]

@Zot

is there a way where I can define the paste row if they isn’t the same as the copied row.

Like one more strRow, so I have one for copy and one for paste?

thank you very much, I really appreciate your help.

 

[Zot]

@Zot

is there a way where I can define the paste row if they isn’t the same as the copied row.

Like one more strRow, so I have one for copy and one for paste?

thank you very much, I really appreciate your help.

In this loop inside the code

For Each nRow In ArryRow
    ws1.Cells(nRow, nCol) = ws2.Cells(nRow, nCol)
Next

it goes through array ArryRow which is E2, E4, E9, E10, E12 (ArryRow(0), ArryRow(1), etc). So, alternative to the loop is by using array index, like:

Dim i as Long
For i = 0 to UBound(ArryRow)
    ws1.Cells(i, nCol) = ws2.Cells(i, nCol)
Next

So if you have 5 different destination to paste like
E2 > G3
E4 > R3
E9 > S1
E10 > A5
E12 > U89

Then you can something like this for example (note you can define array dimension like normal, but I just like to use this method for string array because it is simple for me)

Dim i As Long
Dim strRow As String, ArryDest() As String
Dim strDest As String, ArryDest() As String

' Define Ex to be copied separated by space
strRow = "E2 E4 E9 E10 E12"
strDest = "G3 R3 S1 A5 U89"

' Store strRow as array
ArryRow = Split(strRow)
ArryDest = Split(strDest)

' Can use UBound(ArryRow) or UBound(ArryDest) since they are the same and has to be the same
For i = 0 To UBound(ArryRow)
    ws1.Cells(ArryRow(i), nCol) = ws2.Cells(ArryDest(i), nCol)
Next

 

[Mortenhoey]

Is the last piece the full macro? or do I have to copy/paste the some of the code from #14 to #10 I just has to be as basic as possible, I have to choose the rows myself (both location and destination row) and which workbook to copy from. Because there may come more Workbooks i need data (values) from.

 

[Mortenhoey]

Is there a way the macro only read vertical? so i write the row it has to look up the last value. So something like this:

str.Row = 2 4 9 10 12
str.Des = 3 6 8 10 13

So the macro basicly has to look in the row vertically read though the values and copy the value (next to the value there is an empty cell)

Next in the other workbook look at row 3 (fx) an when there is an empty cell paste the value into the cell. This will do it for me, because then i can use it every month and only has to chance the str.Row, str.Des and the path of the workbook copying from.

Just an example

If I could I would send you the two workbooks.

 

[Zot]

Is the last piece the full macro? or do I have to copy/paste the some of the code from #14 to #10 I just has to be as basic as possible, I have to choose the rows myself (both location and destination row) and which workbook to copy from. Because there may come more Workbooks i need data (values) from.

This was just example on how it can be looped. I thought you are able to integrate into the previous code.

My idea is you will change strRow (the source row) and strDest (the destination row) for data to be copied. That's is the only part you need to change.

 

[Zot]

Is there a way the macro only read vertical? so i write the row it has to look up the last value. So something like this:

str.Row = 2 4 9 10 12
str.Des = 3 6 8 10 13

So the macro basicly has to look in the row vertically read though the values and copy the value (next to the value there is an empty cell)

Next in the other workbook look at row 3 (fx) an when there is an empty cell paste the value into the cell. This will do it for me, because then i can use it every month and only has to chance the str.Row, str.Des and the path of the workbook copying from.

Just an example

If I could I would send you the two workbooks.

Don't really understand what you were trying to say here. I have no idea how to determine which cell to copy to which destination cell. Perhaps you can give captures of source data and also destination data and explain how you decide which data to transfer if possible to do it automatically.

 

[Mortenhoey]

I need a macro to look up a given row:

- The macro should be run in the workbook: photo 1
- All my data is in the workbook in photo 2 (maybe more workbooks to come where I need data from)
- In photo 3 is all the data I need for the given month. The cell number in photo 3 is from february and now i need the data from march (location of the given data in the workbook in photo 2).
- Then i fx need to look up a given row, lets say xDSL @ 50 Mbps in row 145 then i need to copy the value in CL145 and past it into the workbook in photo 1 in the right cell.

Therefore i thought it was easier just to make a macro which lets me choose the workbook I want to copy from and which row and paste it in a workbook where i define which row it has to be in.

Photos is attached

 

[Mortenhoey]

Like fx:

I need to select and copy the next last row that has an empty value nex to it. example cell A1:A100 have data and the next cell is A101 is empty.

when user run the macro it should copy the cell A100, because A101 is empty

When it paste i has to look again in fx B1:B100 and if B101 is empty paste the value from A100 to it.

Then the problem is i has to do this in all the rows i have in photo 3.