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

[TTC]

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

[MichaelAnnis]

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!

[VishnuReddy1997]

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

[TTC]

@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?

[TTC]

@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.

[MichaelAnnis]

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!

[VishnuReddy1997]

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

[TTC]

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

[TTC]

@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!

[VishnuReddy1997]

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