getting next column and filename

S

Sam Hodo

Hi;

I need to copy a range of data
Range("B28").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

then I paste this in a new spreadsheet. (this is no problem)

Now my question..
1. I need to write the filename at the top of the column that the data came
from.
2. Select the next column so that I loop (to paste the next files data into..)

this process will repeat about 600 times..

Thank you for your time and efforts..
 
B

Bernie Deitrick

Sam,

Only one problem - you cannot use 600 columns on one sheet. But you can put the data onto separate
sheets: the code below will put data from 100 files onto each sheet. Run the macro below, and
select the files that you want to process. (I'm not sure if there is an upper limit on the
multi-select feature of the workbook open dialog, but you can always do this in multiple batches.)
Note that this may take a loooooong time, depending on file size, since you are opening and closing
600 files.

HTH,
Bernie
MS Excel MVP

Sub OpenMultipleUserSelectedFiles()
Dim filearray As Variant
Dim mySht As Worksheet
Dim i As Integer

Set mySht = ThisWorkbook.ActiveSheet

filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
Range("B28", Range("B28").End(xlDown)).Copy
If mySht.Range("IV1").End(xlToLeft).Column > 100 Then
Set mySht = ThisWorkbook.Worksheets.Add
End If
With mySht.Range("IV1").End(xlToLeft)
.Cells(2, 2).PasteSpecial xlPasteValues
.Cells(1, 2).Value = filearray(i)
End With
ActiveWorkbook.Close False
Next i
Else:
MsgBox "You clicked cancel"
End If
End Sub
 
Top