Preserve the format (borders, bold, color etc) of the header when copying and creating new sheets

[Priisek (Priya)]

Hello all,

I have an automation use case for which I would like to know the possibility of achieving it. I have explained my use case below.

I have an Excel File, let’s call it A, which has n no of columns and n of rows. The column headers have some format applied to it (bold, font color, font type). I need to create a new excel file B with multiple sheets based on the data present in the excel file A. I need to group the data from Excel file A based on the values present in the column B1 and create a sheet for every single unique value present in the col B1. For example, I need to create 3 sheets (abc, def, ghi) in Excel File B and have all the related data in there.

I am wondering if there’s a way to preserve the header formatting when I am creating these new sheets in Excel File B.

Thanks in advance!

[Adrian_Brinas (Adrian Brînaş)]

Hello, I had tried this example first with the Use Excel Activity.
At the Excel File, I had selected the A.xlsx file.
And also can be used as a template ( it is an option for template ).

Then I had used the Save Excel File As activity and selected B.xlsx
The Replace Existing option should be also selected.

Next, I had used a For Each Excel Row activity.
At the In Range field I have:
Excel.Sheet(“Sheet1”).Range(“2:10”)

In the body of this For Each I created a new variable name
( this can be set with the Set Variable Activity )
name = CurrentRow.ByIndex(1).ToString

Then I checked, if it isn’t already another worksheet with this name.
A simple If condition can be used for this important verification.
The condition is the following:
Excel.Sheet.Item(name) Is Nothing

If this condition is checked, you can use a Duplicate Sheet activity.
At sheet name, you can use:
Excel.Sheet(“Sheet1”)
And the worksheet name will be the variable name.

After the For Each Excel Row loop, you can use a SaveExcelFile
The argument for this activity is just Excel, as a document.
Finally, this project looks like this:

Hope this could be of any help.
Best regards, Adrian