Copy Range Keeping Formulas

[sereganator (Sergey)]

Hi everybody!
How to make the robot copy yesterday’s lines while keeping the formulas?
Tried it through database filtering, but this way copies without formulas. I think it might work out with the help of the LookUp Range activity to find all the lines for yesterday and then transfer the index of these lines to the Copy Paste Range activity. Please tell me how to do this, thanks!
Before copying

and how it should be after copying

Sample1.xlsx (10.2 KB)
Main.xaml (6.2 KB)

[alexandretperez (Alexandre Toledo Perez)]

Hi @sereganator ,

Did you already tried use Copy/Paste Range activity?

It worked!

[sereganator (Sergey)]

Thanks, but the problem is that we don’t know the DestinationCell and SourceRange in advance. Using LookUp Range, find all the lines for yesterday, extract the index of these lines and pass it to the Copy / Paste Range. Insert immediately after previous lines into blank lines.

[alexandretperez (Alexandre Toledo Perez)]

Hi @sereganator,

The sample that you provided has 3 lines for each set of data. This is pattern or it can vary?

The latest rows will always be yesterday?
I’m asking because I didn’t understood why exactly you need copy previous values, you can use Append Range and set the formula in “Sum” column as you need.

Is the formula always in the first row of a date’s group?

I can share the solution, just help me with this questions first.

[sereganator (Sergey)]

The robot will run every day. For example, the robot started on 30.04.2021 and found the date for yesterday 29.04.2021. It copies yesterday’s date and changes it to 30.04.2021, while the formulas should be preserved.

[alexandretperez (Alexandre Toledo Perez)]

Hi @sereganator

Check if this solution helps you:

ExcelUpdate.xaml (11.1 KB)

Let me know if you have any questions.

[sereganator (Sergey)]

Thank you so much! There is a question. For example, in cells A9 and A10 there are formulas. How to do the same, but so that when copying, it changes the date in only one cell? Use the first index?

Sample1 (2).xlsx (10.2 KB)

[alexandretperez (Alexandre Toledo Perez)]

Hi @sereganator,

I updated the sample:

ExcelUpdate.xaml (9.5 KB)

Its simpler now.

[sereganator (Sergey)]

Thanks a lot! If I may, the last question. For example, from another excel file, you need to copy the value from the “NewExtractCost” column to the “NewCost” column. To define a cell, you need to compare the date and add it to the “NewCost” column. How to do it?

Consolidated.xlsx (8.7 KB)

[Pankaj.Patil (Robot Master)]

@sereganator,

Can you select property Preserve Format of read range and write in other sheet?

Cheers,
Pankaj