power automate desktop copy and paste excel with the same formatting

[Tracy1]

I have a question, that is how to copy an excel sheet and paste in a new created worksheet with the same formatting?

[VishnuReddy1997]

Hi @Tracy1 ,

You can achive this by using Run Vbscript action.The Vbscript to do the above scenario is provided below. Please change the paths of the file.

Dim objExcel, objWorkbookInput, objWorkbookOutput
Dim objWorksheetInput, objWorksheetOutput
Dim inputFilePath, outputFilePath
Dim newSheetName

' Define the paths to the input and output Excel files
inputFilePath = "input.xlsx"
outputFilePath = "output.xlsx"
newSheetName = "CopiedSheet" ' Name of the new sheet to be created

' Create an instance of Excel
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False ' Set to True if you want to see the process

' Open the input Excel workbook
Set objWorkbookInput = objExcel.Workbooks.Open(inputFilePath)

' Open the output Excel workbook
Set objWorkbookOutput = objExcel.Workbooks.Open(outputFilePath)

' Assume data is on the first sheet of the input workbook
Set objWorksheetInput = objWorkbookInput.Sheets(1)

' Add a new worksheet to the output workbook
Set objWorksheetOutput = objWorkbookOutput.Sheets.Add
objWorksheetOutput.Name = newSheetName

' Copy all data from the input worksheet
objWorksheetInput.UsedRange.Copy

' Paste the data into the new worksheet in the output workbook
objWorksheetOutput.Range("A1").PasteSpecial -4163 ' -4163 corresponds to xlPasteAll

' Save and close the output workbook
objWorkbookOutput.Save
objWorkbookOutput.Close

' Close the input workbook without saving
objWorkbookInput.Close False

' Quit Excel
objExcel.Quit

' Clean up
Set objWorksheetInput = Nothing
Set objWorksheetOutput = Nothing
Set objWorkbookInput = Nothing
Set objWorkbookOutput = Nothing
Set objExcel = Nothing

MsgBox "Sheet copied successfully!"

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Tracy1]

Thank you Vishnu, i will try this method.

By the way, do you know how to click the Worksheet Tab by 'Variable"

Input the 'Variable' in below Tab name.

[VishnuReddy1997]

Hi @Tracy1 ,

“Select tab in window” action in Power Automate Desktop does accept variables. You can use variables in selectors to dynamically interact with UI elements.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Tracy1]

Hi Vishna,

Could you please give me more details or example? 

For example, the sheet tab variable is '%CurrentItem%', how to input it in the activity 'Select tab in Window'?

Thanks & Regards,

Tracy

[VishnuReddy1997]

Hi @Tracy1 ,

You cant directly give %CurrentItem% under select Tab in window action as it supports only UI Element.If your slectors contains the Tab name there you can replace the Tabname with CurrentItem Variable.

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Kaif_Siddique]

Hi @Tracy1 ,

Use Copy Excel worksheet to copies a worksheet from an Excel document and paste it to the Excel document of the same or different Excel instance.

Hope this will help you.

Regards

Kaif

[Tracy1]

Hi Kaif, 

it works. Thanks.

However when I use Copy Excel worksheet activity, I got below message and the Power Automate stopped. Do you know how to handle it?

Thank you.

[VishnuReddy1997]

Hi @Tracy1 ,

The error message you’re seeing is occurring in Excel when you’re trying to copy a worksheet with a name that already exists in the destination workbook. Here’s how you can handle it:

1. You can rename the worksheet before Copying the data using the Rename Excel worksheet action as shown below

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy