Copy and paste in excel with vba

[sandy armstrong]

Hello everyone

I have a question??? can excel copy data from mulitiple worksheets then paste it into a summary workbook then every time the orginal file is change or anything is added it also refects on the summary workbook

I appreactice the help

[Guido Geurs]

It should work when you opens the individual data books and the summary workbook.
Refer the cells in the summary workbook to the cells in the individual data books and save.
Normally by opening the summary workbook, the data books will also open and the cells in the summary workbook will be updated.

[sandy armstrong]

Okay So when i open the origial file it will update the summary workbook? What if i have all in the same workbook will it still up date all records in that summary sheet?

[sandy armstrong]

This is as close as i got to merge documents in the same folder they all have the same headings also do you know what i am doing wrong???
Thaniks in advance...

1. Dim wbkTarget As Workbook
2. Dim wbkSource As Workbook
3. Dim strSource As String
4. Dim strPathName As String
5. Dim lngSheetNumber As Long
   
6. strPathName = "C:\Users\PLS4\Desktop\Client - Copy"
   
7. Set wbkTarget = Workbooks.Add
   
8. Application.DisplayAlerts = False
9. Do Until wbkTarget.Sheets.Count = 1
10. wbkTarget.Sheets(2).Delete
11. Loop
12. Application.DisplayAlerts = True
    
13. lngSheetNumber = 1
14. strSource = Dir(strPathName)
15. Do Until Len(strSource) = 0
16. Set wbkSource = Workbooks.Open(strSource)
17. wbkSource.Sheets(1).Copy
18. after:=wbkTarget.Sheets(lngSheetNumber)
19. lngSheetNumber = lngSheetNumber + 1
20. wbkTarget.Sheets(wbkTarget.Sheets.Count).Name =
21. wbkSource.Sheets(1).Name
22. wbkSource.Close SaveChanges:=False
23. Loop
24. Application.DisplayAlerts = False
25. wbkTarget.Sheets(1).Delete
26. Application.DisplayAlerts = True
27. End Sub

[Guido Geurs]

You want to copy sheet1 from different workbooks into a new workbook???

[sandy armstrong]

Yes i world like every sheet sheet from sheet one copied into new summary workbook. I attached a copy of the workBook