Open files in list and copy data to next available column

[mikey2322]

Hi All,

I have a workbook called Master.xls and in Column A of worksheet FILES I have a list of file paths (i.e. C:\Test1\loc_pts.xls, C:\Test2\loc_pts.xls etc.)

What I would like to do is go down the list of file paths, open each file and copy Column B of the first worksheet in each newly opened file to the next available column in a worksheet called DATA in a workbook named Data.xls which will also be open.

Any help would be greatly appreciated!

Cheers,

Mike

 

[texasalynn]

ok so try this - be sure to make a copy of your file before trying

Sub update_data()
Dim WBCount As Long, i As Long, LC As Long
Dim MFile As String, DFile As String, x As String
MFile = "Master.xls"
DFile = "Data.xls"
Windows(MFile).Activate
'get count of number of files to open/copy data
WBCount = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'loop thru the files and copy Column B into Data file next available column
For i = 1 To WBCount
    'find next column to copy data into
    LC = Workbooks(DFile).Sheets("Sheet1").Range("IV1").End(xlToLeft).Column + 1
    x = Cells(i, 1)
    Workbooks.Open x
    x = ActiveWorkbook.Name
    Range("B:B").Copy
    Windows(DFile).Activate
    Sheets("Sheet1").Select
    Cells(1, LC).Select
    ActiveSheet.Paste
    Workbooks(x).Close False
    Windows(MFile).Activate
    
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub