Copying Data from multiple Sheets in a single excel file

Help Studio
1

Hello ,

I am trying to copy / move data from 12-15 sheets in a single excel file to another excel file.
I tried using for each activity and copy data from one sheet at a time , this is taking a lot of time to move data .

Note: The sheet names will not be constant

Are there any other methods to do this activity quicker ? Thank You

2

Hi @ram977

Assuming that file1 has n number of sheets & the file2 (destination file) should contain all n sheets with same data, you can try execute macro:

Sub CopyDataBetweenFiles()
    
    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet
    Set sourceWorkbook = Workbooks("SourceWorkbook.xlsx")
    Set targetWorkbook = Workbooks("TargetWorkbook.xlsx")
    
    For Each sourceSheet In sourceWorkbook.Sheets
     
        Set targetSheet = targetWorkbook.Sheets.Add
        
        sourceSheet.Cells.Copy
        targetSheet.Cells.PasteSpecial xlPasteAll
        
        targetSheet.Name = sourceSheet.Name
        
    Next sourceSheet

    sourceWorkbook.Close
    
End Sub

Hope this helps,
Best Regards.

3

@ram977

Try doing the same using merge datatable activity and then paste it once into the excel

cheers