I have basically read all the posts on similar subjects and have tried for a few days to make it work but my solution is far from good enough. So now I am asking for you help.
My project: I have a Master workbook that contains the complete sales for the entire business. This is based on input from three different departments. These three departments have their own Excel workbook that they enter information into. The four files are all located in the same directory on a network folder.
What I want to do is to automatically gather all the entries from the three slave workbooks to the master workbook whenever the macro I am trying to create is run. There is no way of knowing how many new entries each workbook will contain and they have to be added so that that they don't overwrite eachother and so that they are put in sequence after the last row in the master workbook.
Now the information to be gathered is located on the first worksheet in all the three workbooks. The destination sheet in the Master workbook is also the first worksheet in the workbook. The structure of the information that I want to copy is equal on all the worksheets meaning that one sale is entered as a new row in the sheet. It is this row that I want to copy to the mastersheet.
What makes it a bit more complicated is that I want to extract certain information from one sheet and different information from other sheets. From one sheet i want to copy the entire row and from another sheet I just want to copy certain figures like Order Number, Customer, Price and so on. Meaning that I will have a different set of what I want to copy depending on what workbook I am copying from.
Here is what I have so far, it is not working by far and I tried to aim for something simple to start with since my vba experience is limited. So far I cannot copy anything into my Destination master file.
Sub Merge() Dim i As Integer Dim Source As Workbook Dim Destination As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set Destination = ThisWorkbook With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "C:\Documents and Settings\nochhau\Desktop\Excelprosjekt" .FileType = msoFileTypeExcelWorkbooks If .Execute > 0 Then 'Workbooks in folder For i = 1 To .FoundFiles.Count 'Loop through all 'Set Paste target first With Destination.Sheets(1) '''''' Here I would then have to make an if to do a different selection based on what input file I have Set Tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1) End With 'Open Souce file Set Source = Workbooks.Open(.FoundFiles(i)) 'Copy and paste to destination Source.Sheets(i).Range("A1").Copy Tgt 'Close source file without saving Source.Close False Next i End If End With On Error GoTo 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True Application.CutCopyMode = False End Sub
So if anyone can help me I would be very grateful!