Auto Open, Copy, Paste, Close

[edam4i]

Hello,
I need to create a workflow in VBA that meets the following criteria please.
I'm new to VBA so not great with code.

I receive multiple spreadsheets with various names.
I need to be able to have the code open each spreadsheet and copy data from ranges in each tabs, there are currently multiple tabs but for now I will just keep it to 2 tabs.

The data that is copied cannot overwrite the previous data copied so each time a new spreadsheet is opened the data copied from the tab must be added to the next available row of data in the main file.

I need this to have a button on the Main File so when I press the button it will open up the spreadsheets in a folder and start copying the selected data one by one until complete.

All help is very much greatly appreciated.

Here are the names of the files;

Files will be stored on Desktop\Report

Main File is called "Main Report"
The files I will be copying from will always be different names
The Tabs however on each file will always be the same in this case 2 tabs, Tab 1 called "Time", Tab 2"Cost"
The data that needs to be copied from these two tabs on each file will be stored in the "Main Report" with 2 Tabs named the same.
I also need something to show that there is an error so if for some reason I receive a file with the tab names changed by accident I will know.

The data ranges I need copied in Each Tab is from A to C:C everything in these columns down to the last row with data.

After the data is copied in each file the file needs to close and a new one open and when complete a message to say "Copying Complete"

Any further questions please let me know.
Thanks,

 

[Zot]

Not really clear. So, this is my assumption
1) You have main file called Main Report.
2) You have files that you need to copy in folder Report on Desktop
3) The Main Report will have Time and Cost sheets like all the files in Report folder
4) You want to append data from those files to Main Report corresponding sheets continuously

So you will copy this code in regular module in Main Report and run but before that amend the path to Report folder.

Sub CopyDataAllFilesInAFolder()

Dim LastRowMaster As Long, LastRow As Long
Dim FPath As String
Dim FName As Variant
Dim rngData As Range
Dim wsMasterTime As Worksheet, wsMasterCost As Worksheet
Dim ws As Worksheet, wsTime As Worksheet, wsCost As Worksheet
Dim wbMaster As Workbook, wb As Workbook

Set wbMaster = ActiveWorkbook
Set wsMasterTime = wbMaster.Sheets("Time")
Set wsMasterCost = wbMaster.Sheets("Cost")

Application.ScreenUpdating = False

FPath = "C:\Users\username\Desktop\Report\"                ' Amend location of all files to extract data from here
FName = Dir(FPath)

On Error Resume Next
Do While Len(FName) > 0
    Set wb = Workbooks.Open(Filename:=FPath & FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
    
    Set wsTime = Nothing
    Set wsCost = Nothing
    For Each ws In wb.Sheets
        Select Case ws.Name
            Case "Time"
                Set wsTime = ws
            Case "Cost"
                Set wsCost = ws
        End Select
    Next
    
    If wsTime Is Nothing Then MsgBox "Sheet Time is not found in" & vbLf & wb.Name
    If wsCost Is Nothing Then MsgBox "Sheet Cost is not found in" & vbLf & wb.Name
    
    ' Assuming data to be copied starts from range A1 to C & LastRow
    ' Copy Time data to sheet Master
    LastRowMaster = wsMasterTime.Cells(Rows.Count, "C").End(xlUp).Offset(1).Row
    Set rngData = wsTime.Range("A1", wsTime.Cells(Rows.Count, "C").End(xlUp))
    rngData.Copy wsMasterTime.Range("A" & LastRowMaster)
    
    ' Copy Cost data to sheet Master
    LastRowMaster = wsMasterCost.Cells(Rows.Count, "C").End(xlUp).Offset(1).Row
    Set rngData = wsCost.Range("A1", wsCost.Cells(Rows.Count, "C").End(xlUp))
    rngData.Copy wsMasterCost.Range("A" & LastRowMaster)
    
    wb.Close False
    FName = Dir
Loop

MsgBox "Copying Complete"
On Error GoTo 0

End Sub