copy and paste from many files to one file...

[transurban1]

Hi.

I am stuck into my work with VBA.
I am trying to copy some range of about 10 files and paste them into one file. Also, I will copy the contents of each range to the different range of one file changing the cells.

Help me.

 

[AnotherHiggins]

This might get you started. I wrote this to combine all excel files in a particular folder into a single workbook. You can tweak the part of the code that selects all data if you don't want all contents from each sheet. NOTE: as written, this will only copy data from the active sheet in each workbook that it opens.

ub z_Combine_Workbooks()

Application.ScreenUpdating = False
Dim fs
Set fs = Application.FileSearch
Dim MyPath
MyPath = InputBox("Please type the path of the folder that contains" & _
    " the Excel spreadsheets that you want to combine", "Enter Path")

With fs
    .LookIn = MyPath
    .Filename = "*.xls"
    .Execute
        Workbooks.Open .FoundFiles(1)
            CombinedWBName = ActiveWorkbook.name
        Range("a1").End(xlDown).Offset(1).Select

    For i = 2 To .FoundFiles.Count
        Workbooks.Open .FoundFiles(i)
        CurrentWBName = ActiveWorkbook.name

'The following assumes you have header information on row 1
'If that is not the case, the replace "a2" with "a1"
        Range(Range("a2"), Range("a2").SpecialCells(xlCellTypeLastCell)).Copy
        Windows(CombinedWBName).Activate
        ActiveSheet.paste
        Selection.End(xlDown).Offset(1).Select
        Application.CutCopyMode = False
        Windows(CurrentWBName).Close

    Next i
End With

ActiveWorkbook.SaveAs MyPath & "\" & Format(Now(), "yyyy-mm-dd") & " CombinedFile.xls"

Set fs = Nothing
Application.ScreenUpdating = True
End Sub

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886