jolivanes Professional

  • Member since Jul 8th 2004
  • Last Activity:
Posts
1,070
Likes Received
13
Points
5,772
Profile Hits
413
  • Here is an example

    This should copy all 4th Columns From All Workbooks into Master providing that all, incl Workbook with code ("Master"?) are saved in one and the same Folder.

    Change references, like Sheet names, as required.


    Code

    1. Sub Get_Columns()
    2. Dim sPath As String
    3. Dim sFil As String
    4. Dim owb As Workbook
    5. Dim twb As Workbook
    6. With Application
    7. .Calculation = xlCalculationManual
    8. .EnableEvents = False
    9. .ScreenUpdating = False
    10. End With
    11. Set twb = ThisWorkbook
    12. sPath = ThisWorkbook.Path & "\"
    13. sFil = Dir(sPath & "*.xl*")
    14. Do While sFil <> "" And sFil <> twb.Name
    15. Set owb = Workbooks.Open(sPath & sFil)
    16. owb.Sheets("data").Columns(4).Copy twb.Sheets("report").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) '<----- Change sheet names
    17. owb.Close False 'Close no save
    18. sFil = Dir
    19. Loop
    20. With Application
    21. .Calculation = xlAutomatic
    22. .EnableEvents = True
    23. .ScreenUpdating = True
    24. End With


    ######################################################################

    Hi,

    When I was trying to use this in a folder with excel files named Image001, Image002 ... ect, some how only the last excel file information is getting copied, any help??





    1. End Sub