HELP---Loading an excel files by macros

Here's one way to do it

Try using this code...modify as needed and triplicate the main block of code because you have 4 files.

Sub CopyFiles()

'Prepare Excel
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

‘Open the first file and activate it
ChDir "C:\My Documents"
Workbooks.Open Filename:="C:\My Documents\FileName1.xls"
Windows("FileName1.xls").Activate
‘Go to the source sheet and copy
'your desired range
Sheets("YourSheetName").Select
Range("A1:B20").Copy
‘Re-activate your main (destination) file
Windows("MainFileName.xls").Activate
‘Go to your desired sheet and psv the data,
‘assuming a dynamic range to be on the safe side,
'and assuming you have pre-formatted the
'destination range
Sheets("YourMainSheetName").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
'Close FileName1.xls
Windows("FileName1.xls").Activate
ActiveWindow.Close

‘Repeat the above block of code 3 more times
'for each of your other 3 files,
'and plug in the file names and path(s)

‘FINALLY, re-activate your main file, and save it
Sheets("YourMainSheetName").Select
ActiveWorkbook.Save

'Re-set Excel
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = False

End Sub

HTH
Tom Urtis

One correction

As you might've guessed, the last line of code in the macro should be
Application.DisplayAlerts = True (not False)

Take 3, gremlins making code disappear so soon before Halloween

Here's the code again, some of it did not show up on previous post:

Sub CopyFiles()

'Prepare Excel
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

‘Open the first file and activate it
ChDir "C:\My Documents"
Workbooks.Open Filename:="C:\My Documents\FileName1.xls"
Windows("FileName1.xls").Activate
‘Go to the source sheet and copy
'your desired range
Sheets("FileName1SheetName").Select
Range("A1:B20").Copy
‘Re-activate your main (destination) file
Windows("MainFileName.xls").Activate
‘Go to your desired sheet and psv the data,
‘assuming a dynamic range to be on the safe side,
'and assuming you have pre-formatted the
'destination range
Sheets("MainFileSheetName").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
'Close FileName1.xls (no need to save)
Windows("FileName1.xls").Activate
ActiveWindow.Close

‘Repeat the above block of code 3 more times
'for each of your other 3 files,
'and plug in the file names and path(s)

‘FINALLY, re-activate your main file, and save it
Windows("MainFileName.xls").Activate
Sheets("MainFileSheetName").Select
ActiveWorkbook.Save

'Re-set Excel
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

HTH
Tom Urtis

: What is the best way to copy data into an existing excel file from 4 other excel files? Whould I set up a Macro? Thanks in advance for your help!!

Thanks Tom!!

Tom, I really appreciate your help on this one. The code worked great!!

Here's the code again, some of it did not show up on previous post: Sub CopyFiles() 'Prepare Excel Workbooks.Open Filename:="C:\My Documents\FileName1.xls" Sheets("FileName1SheetName").Select Sheets("MainFileSheetName").Select (no need to save) Sheets("MainFileSheetName").Select Application.DisplayAlerts = True End Sub

: Try using this code...modify as needed and triplicate the main block of code because you have 4 files. : : Sub CopyFiles() : 'Prepare Excel : Application.ScreenUpdating = False : Application.EnableEvents = False : Application.DisplayAlerts = False : ‘Open the first file and activate it : ChDir "C:\My Documents" : Workbooks.Open Filename:="C:\My Documents\FileName1.xls" : Windows("FileName1.xls").Activate : ‘Go to the source sheet and copy : 'your desired range : Sheets("YourSheetName").Select : Range("A1:B20").Copy : ‘Re-activate your main (destination) file : Windows("MainFileName.xls").Activate : ‘Go to your desired sheet and psv the data, : ‘assuming a dynamic range to be on the safe side, : 'and assuming you have pre-formatted the : 'destination range : Sheets("YourMainSheetName").Select : Range("A65536").End(xlUp).Offset(1, 0).Select : Selection.PasteSpecial Paste:=xlValues : Application.CutCopyMode = False : 'Close FileName1.xls : Windows("FileName1.xls").Activate : ActiveWindow.Close : ‘Repeat the above block of code 3 more times : 'for each of your other 3 files, : 'and plug in the file names and path(s) : ‘FINALLY, re-activate your main file, and save it : Sheets("YourMainSheetName").Select : ActiveWorkbook.Save : 'Re-set Excel : Application.ScreenUpdating = True : Application.EnableEvents = True : Application.DisplayAlerts = False : End Sub : : HTH : Tom Urtis :