Hi, hoping someone can help out a clueless person here :)

We have a massive amount of Metadata store in excel files that needs to
be converted from one layout to another - a macro seems like the
sensible thing to save me from weeks of tedious copying and pasting.

I have tried to have a go at this with the following code I found on
usenet by Bob Phillips:


Code:
--------------------
Sub ProcessFiles()
Dim oThis As Worksheet
Dim oFSO As Object
Dim oFiles As Object
Dim oFile As Object
Dim sFolder As String
Dim oFolder As Object
Dim i As Long

Application.ScreenUpdating = False

Set oThis = ActiveSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFolder = "C:\MyTest"

If sFolder < "" Then
Set oFolder = oFSO.GetFolder(sFolder)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=oFile.Path
With ActiveWorkbook
oThis.Cells(1, "A").Value = .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next oFile
End If ' sFolder < ""

Application.ScreenUpdating = True

End Sub
--------------------


This sucessfully copies cell 1,A from a workbook in the "c:\MyTest"
folder to the active spreadsheet, but I can't figure out how to specify
a range to copy (I tried the following)


Code:
--------------------
oThis.Cells("1,A":"20,"A").Value = .ActiveSheet.Range("A1").Value
--------------------


But that just gave my a synax error. If anyone could shed any light on
this I would be eternally greatful! :)

Thanks
jonny.


--
jonnyreeves
------------------------------------------------------------------------
jonnyreeves's Profile: http://www.excelforum.com/member.php...o&userid=31308
View this thread: http://www.excelforum.com/showthread...hreadid=509993