• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple Workbooks

  • Thread starter Thread starter GB
  • Start date Start date
G

GB

Member
Hi,
I am trying to merge multiple workbooks in one workbook on one worksheet using some vba.
  • All workbooks that I want to merge together are in the same file directory.
  • All workbooks have multiple worksheets, but I am only interested in merging the worksheets named "abc".
  • I want the data from each worksheet (named "abc") from each workbook to be pasted below each other. For example workbook 1 will fill up the range A1:D10, workbook 2 will start from cell A11 and so on until all data has been bought together.
I have tried various options without success. Do you have some vba I can adopt for this job?

Thanks if you.
cheers
GB
 
Hi GB

You should be able to adapt the following by changing the path and take note of the sheet code name (sheet1) so change that to suit.

Code: 
Sub OpenImp() 'Excel VBA to open and import data from the Excel files in a directory
    Const sPath = "C:\Test\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim ws As Worksheet

    Set ws = Sheet1 'change if relevant
    sFil = Dir(sPath & "*.xl*")
    Do While sFil <> "" 'Only Copies Cols A to F.
        Set owb = Workbooks.Open(sPath & sFil)
        Sheets("ABC").Range("A1:D10").Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Close no save
        sFil = Dir
    Loop
End Sub

If ther are formula on the files you are opening you may only want to paste the values. The code need only minor adjustments.

Take care

Smallman
 
Smallman said:
Hi GB

You should be able to adapt the following by changing the path and take note of the sheet code name (sheet1) so change that to suit.

Code: 
Sub OpenImp() 'Excel VBA to open and import data from the Excel files in a directory
    Const sPath = "C:\Test\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim ws As Worksheet

    Set ws = Sheet1 'change if relevant
    sFil = Dir(sPath & "*.xl*")
    Do While sFil <> "" 'Only Copies Cols A to F.
        Set owb = Workbooks.Open(sPath & sFil)
        Sheets("ABC").Range("A1:D10").Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
        owb.Close False 'Close no save
        sFil = Dir
    Loop
End Sub

If ther are formula on the files you are opening you may only want to paste the values. The code need only minor adjustments.

Take care

Smallman
Click to expand...

Thanks Smallman,
I have tweaked your code to suit. Its works perfectly.

Thanks so much.
GB
 
You must log in or register to reply here.
Back
Top