Copy range from 1 excel file (SAP Report) to new excel file with running number

Hello all,

I’m working on a project with the objective to retrieve supporting document (invoice) from our internal system based on a report generated in SAP.

Here are the steps:

  1. Download report from SAP & save as “SAP Report 1,2,3,4,5…(continuously)”
  2. Extract data from 2 columns in SAP Report → I’m copying 2 column named Billing Document & Company Code from Sheet 1 & put it in Sheet 2 (so my Sheet 2 will only show Billing Document & Company Code column)
  3. Copy data from Sheet 2 to new excel file called Template 1,2,3,4,5…(continuously). The data from SAP need to be paste here in Template 1,2,3,4,5… as I need it to be presented in certain layout
  4. Template 1,2,3,4,5 can only have 99 line item max. Meaning:
    → if SAP Report 1 has 50 line items, it will paste into Template 1 (then the BOT need to check on the next SAP Report)
    → if SAP Report 2 has 150 line items, it will paste into Template 2 for 99 lines and the remaining 51 line in Template 3 (then the BOT need to check on the next SAP Report)

This will be in loop till there is no more SAP Report to be process

  1. Template 1,2,3,4,5… then will be uploaded into our internal system for bulk invoice downloading.

Need Help With
I have 3 SAP Report to test. (SAP Report 1,2,3). I manage to figure out Step 1 & 2 above. However when it comes to Step 3, the BOT only copy from SAP Report 3 to Template 1. I dont understand what is happening. Can someone help? Here are the screenshot FYR. Thanks.


  1. In ‘Read Cell’ there is a variable called “CellA2”
  2. In ‘Read Range’ there is a variable called “CellRange”