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

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
@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
VBA 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:
VBA Code:
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) :)
VBA Code:
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

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 26, 2020
Messages
1,171
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • 1 - Destinationworkbook.PNG
    1 - Destinationworkbook.PNG
    85.5 KB · Views: 1
  • 2 - Location workbook.PNG
    2 - Location workbook.PNG
    108.4 KB · Views: 1
  • 3 - Row in need data from.PNG
    3 - Row in need data from.PNG
    72 KB · Views: 1

Mortenhoey

New Member
Joined
Apr 12, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,917
Messages
5,638,981
Members
417,061
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top