No result when copying from workbook to workbook

[christianhau]

Hi!

I am trying to write a vba macro that loops through a folder of workbooks and copies some information from each workbook (Sheet1), to a master workbook that should have all the infor from the other workbooks. I am not receiving any error messages but I am not getting any results either so i am kind of stuck, I would appreciate any help! I try to write to the files with the "Test" string but I can only write it to the source files and not to the master file that I am trying to add the information to...

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
'Open Workbook x and Set a Workbook variable to it
Set Source = Workbooks.Open(.FoundFiles(i))
Cells(3, 4).Value = "TEST"
Source.Range("A1").Select
Selection.Copy

Destination.Activate
Destination.Range("A1").Select
Cells(3, 4).Value = "TEST"
Selection.Paste
'Source.Close




Next i
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub

[mdmackillop]

What version of Excel are you using?

[Norie]

It's not clear where you are copying to/from and the main reason for that is the unqualified Cells references.

If you don't qualify them they will refer to whatever VBA regards as the activesheet/workbook.

And when dealing with multiple workbooks it really is best to explicitly reference worksheets etc.

[christianhau]

Hi!

Thanks for the reply, I am using Excel 2003 sp3.

I realize that it is not clear enough where I am copying from and where I want to paste it, but I honestly don't understand how I can make it clearer except for referencing to the worksheet. The worksheet is called Sheet1 (default) in both workbooks.

[mdmackillop]

I can't test if your source file is opening, but try

If .Execute > 0 Then 'Workbooks in folder
For i = 1 To .FoundFiles.Count 'Loop through all
'Set Paste target first
With Destination.Sheets(1)
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

[christianhau]

All my sourcefiles are opening nicely but the content is still not being copied, and since there are no error messages I am struggling with figuring out what the problem is...