Copy/Paste from three sheets into one?

Mesh1o,

See if this will get you started. You are going to have to make some modification because I don't know here you files are located. I have this set up so it will loop through 3 appendix files. If you have more, you can simple change the loop to however many it is that you have. You may also need to look at the worksheet names unless your files are using the Sheet1, Sheet2, etc. defaults.

I am giving you a sub and a function. The function simply sees if the file is open and if it is not it opens it.

Sub aaa()

Dim wsApp As Worksheet, wsCopy As Worksheet
Dim lPass As Long
Dim sFileName As String, sFilePath As String

If WorkbookIsOpen("copy1.xls") <> True Then
    Workbooks.Open Filename:="C:\temp\copy1.xls"
End If

Set wsCopy = Workbooks("copy1.xls").Sheets("Sheet1")

For lPass = 1 To 3
        
    sFileName = "Appendix " + CStr(lPass) + ".xls"
    sFilePath = "C:\temp\" + sFileName
    
    If WorkbookIsOpen(sFileName) <> True Then
        Workbooks.Open Filename:=sFilePath
    End If
    Set wsApp = Workbooks(sFileName).Sheets("Sheet1")
    wsApp.Range("A9:G18").Copy
    wsCopy.Range("A" & CStr(wsCopy.Cells(Rows.Count, 1).End(xlUp).Row + 2)).PasteSpecial
    Set wsApp = Nothing
    Windows(sFileName).Close

Next lPass

End Sub

Public Function WorkbookIsOpen(wbname) As Boolean

' Returns TRUE if the workbook is open
    
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then
    WorkbookIsOpen = True
Else
    WorkbookIsOpen = False
End If

End Function

I hope this helps you get started.