copy data from all workbooks(in D drive) from column A and paste in required sheet.

[Amit Modi]

I have 10 workbooks its named as 1.xlsx, 2.xlsx, 3.xlsx to.....10.xlsx. Its location is “D:\Data\ “ in Data folder.

My required data are in column B in each workbook.

i want all data in column A in my target book named “targert.xlsx”.

I want this using one button click please help me for the same.

Please find further details and sample workbook in attached sheet.

Thank u very much.

 

[Somendra Misra]

Hi Amit,

So in target file starting from A1 first all 1.xlsx B column data should populate than 2.xlsx data and so on till 10.xlsx?

Is it so?

Regards,

 

[Amit Modi]

yes absolutely

 

[Somendra Misra]

@Amit Modi

See the file. Change the directory in the code where I mentioned in the code.

Regards,

 

[Smallman]

Hi Amit

This should hit the mark quite efficiently. Just remember to change the path.

Option Explicit
Sub OpenImp() 'Excel VBA to open and import data
    Const sPath = "C:\Test\" 'Change to suit
    Dim sFil As String
    Dim owb As Workbook
    Dim ws As Worksheet

    Set ws = Sheet1
    sFil = Dir(sPath & "*.xl*") 'Flexible enough to handle all XL file types
    Do While sFil <> "" 'Only Copies Cols B
        Set owb = Workbooks.Open(sPath & sFil)
        Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
        owb.Close False'Close No need to save
        sFil = Dir
    Loop
End Sub

There is no need to save the files as you are only opening them to get the data. It is just a use and abuse type setup.

see also article 2.

Importing Files No Fuss

Take care

Smallman

 

[MaunishP]

Hi Smallman, what if i want to copy data from Column A1 to P9784.

How this macro will work?

Regards,
Maunish Patel

 

[Smallman]

Hi

Remove this line:

Range("B1", Range("B" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)

And change to this line:

[A1:P9784].copy ws.Range("A"& Rows.Count).End(xlUp)(2)

Take care

Smallman