Quick Way to Append an Excel File as a Sheet onto Another File

Hi ankini,

This can be done with a VBA macro.
Copy the code below into a module in the SOURCE workbook, the one with the summary sheet you want to copy across.

In the code, there are a couple of bits which you need to update before running:

• The name of the sheet you want to copy. Replace "Sheet1" with whatever your sheet is called.
  The location (folder) where your 90 files are. Ensure the file path is in double quotation marks and has a final back-slash before the closing double quotations.

The code will run through all files and insert the source sheet in all .xlsx files in the specified folder.

Option Explicit

Sub copyWorksheetToAll()

Application.ScreenUpdating = False

' Set SOURCE workbook/worksheet
    Dim sourceWB As Workbook
    Dim sourceWS As Worksheet

    Set sourceWB = ThisWorkbook
    Set sourceWS = sourceWB.Sheets("Sheet1")   ' <<< Update sheetname !!!

' Creates collection of filenames to be appended
    Dim loopFolder As String
    Dim fName As Variant
    Dim myFiles As New Collection

' Enter folder name where files are to be appended
' ### Ensure backslash before end double-quote  !!!  ###
    loopFolder = "C:\Users\Sample\Desktop\CopyTest\"
    fName = Dir(loopFolder & "*.xlsx")

    Do While fName <> ""
        myFiles.Add fName
        fName = Dir
    Loop

' Loops through all files & add copy of sheet
    Dim wb As Workbook

    For Each fName In myFiles
        Set wb = Workbooks.Open(Filename:=(loopFolder & fName))
        sourceWS.Copy after:=wb.Sheets(1)
        wb.Save
        wb.Close
    Next

Application.ScreenUpdating = True

MsgBox "Sheet had been copied into all .xlsx files in the folder.", vbInformation, "Task completed"

End Sub

PS: For testing, use copies of your files or ensure you have them backed up.