I've got 500 workbooks, all formatted the same. I want to copy 2
columns from each of the workbooks to a new workbook. I'm not sure I
can have 1000 columns in Excel, but I can split the process into
groups. Anyway, I was testing my code with a couple of files and it's
not working. What I end up with is an empty worksheet. Also. must
past special to get just the values, because the workbooks all have
formulas in them. I've never done this particular bit before. Not
sure how it should work. Could someone take a look and help me out
with a suggestion or 3?

Thanks,
Jennifer

Sub MoveCols()
Dim XL As Excel.Application
Dim fso
Dim fol
Dim Col1 As Integer
Dim Col2 As Integer
Dim fil
Dim Unit As String
Set XL = New Excel.Application
Set fso = CreateObject("scripting.Filesystemobject")
Col1 = 2
Col2 = 3
Set fol = fso.getfolder("C:\Out\Audits\Test")


For Each fil In fol.Files
XL.Workbooks.Open (fil.Path)
Unit = Mid(fil.Path, InStr(1, fil.Path, ".") + 1)
XL.Worksheets("Final Plan").Columns("S:T").Select
Selection.Copy
Range(Columns(Col1), Columns(Col2)).Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Col1 = Col1 + 2
Col2 = Col2 + 2
Worksheets("Sheet1").Cells(9, Col1).Value = Unit
XL.Quit
Next

End Sub