Copying Data from Excel Sheet1 to Sheet 2

[luca4 (Luca Plassmann)]

I want to copy values from an Excel table (Table 1) and sort by the name in the tables 2.3 and so on. e.g. Value of London in Table 1 should be added to the table ‘London’. Value New York from Table 1 is to be added to table “New York”.

[arjun.shiroya]

Hi @luca4 , welcome to the forum!

1. Use Read Range on Table 1 to get all data into a DataTable (dtSource)

2. Get unique city names: dtSource.AsEnumerable().Select(Function(r) r("CityColumn").ToString()).Distinct().ToArray()

3. For each city name in the array:
   Filter the DataTable: dtSource.Select("CityColumn = '" + city + "'")
   Convert filtered rows to new DataTable
   Write Range to sheet named after the city (like “London”)

VB code for the filter part:

Dim filteredRows = dtSource.Select("Location = '" + cityName + "'")
Dim filteredDt = filteredRows.CopyToDataTable()

Put this in a For Each loop inside Excel Application Scope. Should work perfectly!

Let me know if you hit any issues

[raja.arslankhan (Raja Muhammad Arslan khan)]

@luca4

Solution 1: Using LINQ (Best & Cleanest)

Assign Activities

1. Read Table1 → dtMaster

Then create three new DataTables (or read them if already exist):

• dtLondon
• dtNewYork
• dtDubai
  (Or dynamically create based on unique names)

Assign for London

dtLondon = dtMaster.AsEnumerable().
            Where(Function(r) r("Name").ToString.Equals("London")).
            CopyToDataTable()

Assign for New York

dtNewYork = dtMaster.AsEnumerable().
              Where(Function(r) r("Name").ToString.Equals("New York")).
              CopyToDataTable()

Assign for Dubai

dtDubai = dtMaster.AsEnumerable().
            Where(Function(r) r("Name").ToString.Equals("Dubai")).
            CopyToDataTable()

Then write each datatable back into its respective Excel table.

[MohammedShabbir (shabbir.qs)]

Can you share a screenshot of the sample data, As per the description we can do it by adding a formula or vlookup and extend range. We can use linq too.

[ashokkarale]

@luca4,

Welcome to the community.

Kindly note that always share maximum possible data and information so expert can help you.

Please share the sample input and the output and also what you tried and any error you are facing so we can help you.

[luca4 (Luca Plassmann)]

Every time a new line is inserted, I want the city to be recognized and assigned to the corresponding sheet.

[nishiijain2000 (Nishi Jain)]

Hi @luca4

You can use For Each Row activity to route the rows to the correct city sheet.
1. Read Range (Sheet1 → dtMain)
2. For Each Row in dtMain
3. Get city name → city = row(“City”).ToString
4. Use Write Range / Append Range with sheet name = city
5. This automatically writes London rows into sheet “London”, New York rows into sheet “New York”

Cheers

[ashokkarale]

@luca4

Follow these steps:

1. Read Range from Sheet1
2. Get distinct cities using LINQ query into an string array variable like

arrCities = (From row In table.DefaultView.ToTable(True, "City").AsEnumerable
 Select row("City").ToString).ToArray

3. Now use For Each activity to iterate arrCities
4. Inside it, use Filter Data Table activity to Filter City column = currently iterating item and get it in a temporary datatable e.g. dtFiltered
5. After filtering data use Write Range activity to write dtFiltered