Copying excel single row B-L , paste until last entry column M

B

BluePrismDeveloper

New Member
Hi,

i have data pasted in an Excel starting from Column M to column Z , multiple lines.

Also have vlookups in the columns B- L, which i want to paste in all rows B-L , until it recognizes the last entry posted on M ( like the "fill"option in Excel ).
I copy the first line B-L , then paste it in the last empty cell found in column B. But how can i get BP to paste B-L so they match the last line entered from M?

Many thanks !
Ralph
 
V

VJR

Well-Known Member
Hi BluePrismDeveloper,

-You have data in Columns M to Z lets say upto Row #10
-You have vlookup formula in Column B to L only in Row #1 (so B1 to L1)
- Now you want to copy the data with formula from B1 to L1 to B10 to L10 (because M to Z is only till 10th row)
Is this the correct understanding of your requirement?

If so below is what you need to do:
- Get the LastRow data item derived from the 'Get Number Of Rows' action of MS Excel VBO
This will store 10 in LastRow data item.
- In the 'Copy and Paste Worksheet Range' action,
give Source Range parameter as - "B1:L1"
give Destination Range parameter as - "B" & [LastRow] & ":L" & [LastRow]

So if LastRow = 10, then the formula after running the process will be evaluated as "B10:L10"

Do ensure to take care of the usage of the $ sign in the formula designed in Excel. You can also add the $ sign in the above destination range parameter.
 
B

BluePrismDeveloper

New Member
Hi VJ,
thanks for the clear input, it works great !
it indeed copies the source range B4 :L4 to the last row, but i also would like to fill the rows inbetween , can i adjust the code in the destination range easily to get that result?

Many thanks , again
Bets regards Ralph
 
V

VJR

Well-Known Member
Hi BluePrismDeveloper,

I didn't quite get what you meant by "fill the rows inbetween". Are you able to elaborate or even better provide a visual representation with the help of screenshots?
 
B

BluePrismDeveloper

New Member
Hi VJ
now, it copies cell B4:L4 and pastes this into B4000:L4000, as a single line. But cells B5 until L39999 remain empty, which is the section in the middle which actually should be filled.

Many thanks !!!!
Ralph
 
V

VJR

Well-Known Member
Hi BluePrismDeveloper,

Yes, you need to do what you are already thinking of which is to modify the Destination Range.

Destination Range parameter - "B5" & ":L" & [LastRow]

(Or even this is fine -> "B5:L" & [LastRow] )

How the data will look like will also depend on the formulas you have used.
So use the above destination range and post back if you are facing any issues.
 
You must log in or register to reply here.
Top