This novice is stumped. I have reviewed the blogs etal on vba and
cannot get over this hurdle.
Objective - vba code to open up 50 files in a directory, one at a time
(no on linksupdate), copy a consistent named range "deptupload" to a
master worksheet which contains this vba macro., then close file.
FileName = 75 aUpload Dbase.xls is master consolidating 'to' file,
which contains this vba. Sheet name for compiling the data is
'upload' in this master file.
....Needs to perform the copy/pastevalues at end of range from the
previous copy/pastevalues.
Below is my humble attempt...erroring at the Workbooks.Open command,
which requires filename, which is the next line. However, I clearly
have not coded correctly.
I could easily place the 50 specific file names in a list in the
master file if this simplifies matters....with or without the full
directory path.
fyi....I am in Excel 2003....(after finding after 2hrs the
Application.FileSearch will not work in Excel 2007).
Thanks so much, Mike.
Sub BuildUpload()
'
' BuildUpload Macro
' Macro recorded 1/11/2009 by Mike Salmon
'
' Keyboard Shortcut: Ctrl+y
'
With Application.FileSearch
.LookIn = "C:\Documents and Settings\HP_Administrator\My Documents
\Salmon Michael\DBMs"
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute
End With
cnt = Application.FileSearch.FoundFiles.Count
For i = 1 To cnt
Workbooks.Open
Filename = Application.FileSearch.FoundFiles.Item(i), UpdateLinks
= 2
Range(deptupload).Select
Selection.Copy
Windows("75 aUpload Dbase.xls").Activate
Range("B" & i + 1).Select
Selection.PasteSpecial Paste = xlPasteValues
Operation = xlNone
ActiveWindow.ActivateNext
ActiveWindow.Close
Next i
--~--~---------~--~----~------------~-------~--~----~
Visit the blog to download Excel tutorials at
http://www.excel-macros.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/excel-macros?hl=en
Visit & Join Our Orkut Community at
http://www.orkut.com/Community.aspx?cmm=22913620
To Learn VBA Macros Please visit http://www.vbamacros.blogspot.com
To see the Daily Excel Tips, Go to:
http://exceldailytip.blogspot.com
If you find any spam message in the group, please send an email to Ayush @
jainayus...@gmail.com
-~----------~----~----~----~------~----~------~--~---
