Paste Data to Excel Previously Done Using AutoFill

Hi all,

I have a spreadsheet that looks similar to this (only many more rows on the actual). Notice how on line 20, there is a line break and the same data is repeated after the break.

I will have a DataTable with a list of items that I want to paste into the “Item” column here. So say my list of items is:
12345
54321
67891

I would want to paste each item into the “Item” column, and then once at the line break, paste the next item, and so on. So it would look like this:

Does anyone have any ideas on how to automate this process? Thanks!

How many rows of data are we dealing with? Where do you get the data to be filled in?

The rows of data may de different each time. I have about 10 different templates similar to above, this is just an example of 1 of them.

I would have a DataTable with the item numbers and it already will know which template to use because I’m obtaining the DataTable from filtering another DataTable. I just cannot figure out how to get it to paste each item number down, but stop at the line break, and then paste the next item number down, and so on.

Instead of pasting the data directly to the table, paste it into another sheet in excel and run a macro to fill data in the table.

I’m not too familiar with Macros…could you possibly show an example of what you mean for this case?

If you are new to excel macros, it will be difficult to explain how to do this. Try learning macros. Below will be the steps you have to follow.
Let us call the sheet with the table as TableSheet and the one with data DataSheet.

  • Run a loop on each row in the DataSheet
  • For each value go the the TableSheet and paste the value until you encounter a blank in column A.

I recommend learning excel vba.

1 Like

Hi @Luecke_Anthony

If you provide some dummy excel then it will be easy to work on it.

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

Sure - here are two dummy excels. DataSheet.xlsx (15.6 KB) TableSheet.xlsx (16.8 KB)

DataSheet - contains a sample of what my data may look like in Excel before converting to a DataTable.

TableSheet- contains a sample of what my Excel sheet will look like where I want to paste my data.

So for each “Item” in my DataSheet, I want to paste it to the “Item” column on my TableSheet all the way down until the line-break in TableSheet. I then want to take the next item from DataSheet and do the same, ensuring to start after the line break, so it doesn’t overwrite what was just written, and so on.

My final TableSheet should look like this:

Hi @Luecke_Anthony

I have created the workflow which gives accurate output that you want :-
Main.xaml (13.2 KB)
TableSheet.xlsx (16.9 KB)
DataSheet.xlsx (15.6 KB)

Mark as solution and like it :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

1 Like

Thank you! This worked, I will go through the workflow to figure out the logic :slight_smile:

Hi @Luecke_Anthony

You are welcome :slight_smile:

Happy Automation :raised_hands:

Best Regards
Er Pratik Wavhal :robot::man_technologist:t4: :computer:

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