Copy paste range with dynamic range

I have two excel sheets. One is report which is generated every day with a different number of lines. These lines should be added to the other excel sheet at the beginning below the two header rows.

Works so far fine with “Insert Rows”, but not with “Copy Paste Range”.
I have this range for example with the daily report and five rows of data.

A fix range like this works fine.
ExcelDownload.Sheet(“Daily Report”).Range(“A3:Y7”)

But the range changes with the number of lines in the daily report, e.g.
1 line the range is A3:Y3
2 lines is A3:Y4

5 lines is A3:Y7

How can I use a dynamic range? Is there maybe an VB expression which I could use.

Yes I know about “Append Range” which would be a lot easier and works great, but the User wants to have the new lines at the beginning of the sheet. Filter is also not an option because of “merged cells”. :frowning:

Any ideas?

Use For Each Row Activity and put Index Number as variable name like Indexnum

image

then Inside use your activity in which you use this: ExcelDownload.Sheet(“Daily Report”).Range(“A3:Y7”)
So instead using this give condition like: ExcelDownload.Sheet(“Daily Report”).Range(“A3:Y”+(indexnum+3).tostring)

I m using indexnum+3 because indexnum start from 0 value and you mention(line the range is A3:Y3).

So Each and every time indexnum will be increase by 1.

Let me know if any doubt

@Sagar_Gupta1 – He posted this question under StudioX… Your solution looks like works for Studio…not on StudioX…

@Daniel_Fichtner - Can you please take a look at my post…i have provided two solutions (one using find last row/column activity and other using formula)…

I see in your case data is growing and you want to dynamically use that range …

Yes you can do it… link to my post …

Please check and let me know…

Thanks a lot guys! This helped a lot!

My solution is:

  • Getting the Total number of rows, with an excel command similar to what prasath17 posted.
  • Mapping this Value with “Value Mappings” to a Variable “NumberOfRows”.

But I just figured that I can just use the total number of lines to get my Corner of lower right side. So I use this now for my Copy Paste Range, which works perfect :slight_smile:

ExcelDownload.Sheet(“Daily Report”).Range(“A3:Y”+NumberOfRows.tostring)

@Sagar_Gupta1: Thanks that was the hint I needed :smiley:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.