Unanswered

how to copy and paste from two different excel sheet from one excel to another excel file

(0) Share

Report

Posted on by TTC Super User 2024 Season 1

Hello all,

I have two different excel files but I need to copy from Excel to another Excel worksheet by using PAD. All I can see so far on the web is how to copy the data in the same excel worksheet but they create another data tab/sheet and paste in the same worksbook. I don't want to copy the same data on one excel and then create a new sheet in the same excel and paste the same data.

As I mentioned, I have two different Excel files. For example, one file called 'DayBreak Fast.xlsx and the other Excel file called R&L Carriers.xlsx. I want to know how to copy the data from Daybreak Fast.xlsx to R+L Carriers.xlsx, but I need to paste the data from column 'E' to row 3 and then I have another file call Rogers.xlsx and I need to copy and paste the value to the same excel file R+L Carrier.xlsx and so on...

But let say the first file ended in row 50, I want the second file to be paste in the column 'E' but to row 51 and so on...

I will skip the column heading on the file that copy from (Daybreak and Rogers). The R&L Carriers excel is like a template file with heading that I want to copy all other excel files into R+L Carriers.xlsx

Categories:

Power Automate Desktop

I have the same question (0)

All responses (9)

Answers (0)

MichaelAnnis 5,727 Moderator on at

Like (1)

Report

This will be 100x faster in Excel VBA, but if you are intent on using PAD:

You'll have to get the list of files somewhere. Easiest way would be if all those files were in a "to be processed" folder, but if not, then you could list them on a spreadsheet or something and pull them in that way or make copies of them to a "to be processed" folder and do the following:

Get files (from to be processed)
Launch Excel R&L (ExcelInstance1)

Get first free row on Column E to %FFR%

For each CurrentItem in %Files%

Launch Excel %CurrentItem% to %ExcelInstance2%

get first free row on column E to %FFR2%

copy cells E,2 to (whatever column you need)%FFR2 - 1%

Paste cells to E and %FFR% IN EXCELINSTANCE (not ExcelInstance2, we want the first one)

Close ExcelInstance2

Get first free row (from ExcelInstance1) on Column E to %FFR% 'have to reset the FFR for the next loop

End for each

This should loop through each file from Get files and paste them into ExcelInstance

Good luck!

Was this reply helpful? Yes No
VishnuReddy1997 2,656 Super User 2025 Season 2 on at

Like (0)

Report
Hi @TTC ,

Please find the solution.

Code:
Please copy the below code to your flow.

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\DayBreak Fast.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance_1 Excel.GetFirstFreeColumnRow Instance: ExcelInstance_1 FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.CopyCellsFromExcel.CopyCells Instance: ExcelInstance_1 StartColumn: $'''A''' StartRow: 2 EndColumn: $'''D''' EndRow: FirstFreeRow - 1 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\R&L Carriers.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance_2 Excel.PasteCellsToExcel.PasteAt Instance: ExcelInstance_2 Column: $'''E''' Row: 2 Excel.CloseExcel.Close Instance: ExcelInstance_1 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\Desktop\\Power Automate Desktop\\Practice\\Excel\\Rogers.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance_3 Excel.GetFirstFreeColumnRow Instance: ExcelInstance_3 FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.CopyCellsFromExcel.CopyCells Instance: ExcelInstance_3 StartColumn: $'''A''' StartRow: 2 EndColumn: $'''D''' EndRow: FirstFreeRow - 1 Excel.GetFirstFreeColumnRow Instance: ExcelInstance_2 FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.PasteCellsToExcel.PasteAt Instance: ExcelInstance_2 Column: $'''E''' Row: FirstFreeRow Excel.CloseExcel.CloseAndSave Instance: ExcelInstance_2 Excel.CloseExcel.CloseAndSave Instance: ExcelInstance_3

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

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like (0)

Report

@VishnuReddy1997

Hi,

Thank you for the visual. Question, I don't see any loop through your script. So, how the script will know to open next file and to copy & paste it to the other file and so on?

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like (0)

Report

@MichaelAnnis

Is there a way I can see your step through PAD? If you show the steps through PAD it will be helpful for me.

Also, I have been Google to get this working through VBA as you mentioned so far I found nothing yet. I will need some kind of visual to see how it get done in VBA.

Thanks for reply to my task.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

I have a PAD/macro combination for combining excel sheets with the same column set here:

https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Entering-the-data-from-more-than-1-excel-...

Good luck!

Was this reply helpful? Yes No
VishnuReddy1997 2,656 Super User 2025 Season 2 on at

Like (0)

Report
Hi @TTC ,

I have made the flow dynamic now.If there are 100's of files in Input Folder it will all copy to output one after other.
Please find the solution.

Code:
Folder.GetFiles Folder: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\Input''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Excel_Files LOOP FOREACH CurrentItem_ExcelFile IN Excel_Files Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem_ExcelFile.FullName Visible: True ReadOnly: False Instance=> Input_ExcelInstance Excel.GetFirstFreeColumnRow Instance: Input_ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.CopyCellsFromExcel.CopyCells Instance: Input_ExcelInstance StartColumn: $'''A''' StartRow: 2 EndColumn: FirstFreeColumn - 1 EndRow: FirstFreeRow - 1 Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\R&L Carriers.xlsx''' Visible: True ReadOnly: False Instance=> Output_ExcelInstance Excel.GetFirstFreeColumnRow Instance: Output_ExcelInstance FirstFreeColumn=> FirstFreeColumn FirstFreeRow=> FirstFreeRow Excel.PasteCellsToExcel.PasteAt Instance: Output_ExcelInstance Column: $'''E''' Row: FirstFreeRow Excel.CloseExcel.Close Instance: Input_ExcelInstance END Excel.CloseExcel.CloseAndSave Instance: Output_ExcelInstance

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

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like (0)

Report

Thank you Sir!

You're correct. the VBA is 100 times faster and I was able to manage the task through VBA. I also have the PAD code for backup

Was this reply helpful? Yes No
TTC Super User 2024 Season 1 on at

Like (0)

Report

@VishnuReddy1997

Thank you sir for the steps through PAD. I will try your solution and let you know if I got issue.

It's always good to know you can accomplish a task by using different application.

Thanks again!

Was this reply helpful? Yes No
VishnuReddy1997 2,656 Super User 2025 Season 2 on at

Like (0)

Report

Hi @TTC ,

Sure please and let me if it is working or Not.

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

Thanks & Regards
Vishnu Reddy

Was this reply helpful? Yes No