Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy cols from one excel file to another.
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Cols To 2 Cols VLookup Comparison | Excel Discussion (Misc queries) | |||
Rows & Cols - copy formulas in Col A to # rows in Col B | Excel Programming | |||
Copy without Hidden Cols - How | Excel Discussion (Misc queries) | |||
Cond Format:re color 2 cols, skip 2 cols | Excel Worksheet Functions | |||
Generate .txt file from 2 cols of data | Excel Programming |