Copy data from one workbook to another of same name
One last problem: The below creats a "Run-time error 1004
Application-defined or object-defined error"
Any help is greatly appreciated
Sub RAW()
Dim PathSrc As String, PathDest As String
Dim srcList As Variant, destList As Variant
Dim i As Long
Dim bkSrc As Workbook, bkDest As Workbook
PathSrc = "Y:\Sales\Target Customer\2005 Raw\"
PathDest = "Y:\Sales\Target Customer\2005 Raw - Main\"
srcList = Array("Raw 1.xls", _
"Raw 2.xls")
destList = Array("Raw 1M.xls", _
"Raw 2M.xls")
For i = LBound(srcList) To UBound(srcList)
Set bkSrc = Workbooks.Open(PathSrc & srcList(i))
Set bkDest = Workbooks.Open(PathDest & destList(i))
bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _
Destination:=bkDest
bkSrc.Close SaveChanges:=False
bkDest.Close SaveChanges:=True
Next
End Sub
-----Original Message-----
Expand the lists to include all of your source and
corresponding destination
files.
Sub CopyFiles()
Dim PathSrc as String, PathDest as String
Dim srcList as Variant, destList as Variant
Dim i as Long
Dim bkSrc as Workbook, bkDest as Workbook
PathSrc = "C:\MyFiles\"
PathDest = "C:\YourFiles\"
srcList = Array("AE1234.xls", _
"AB3567.xls", _
"RN2134.xls", _
"ZZ9999.xls")
destList = Array("bb1111.xls", _
"cc2222.xls", _
"dd3333.xls", _
"zz1111.xls")
for i = lbound(srcList) to ubound(srcList)
set bksrc = workbooks.Open(PathSrc & srcList(i))
set bkdest = workbooks.Open(PathDest & destlist(i))
bksrc.worksheets(1).Rows(1).Resize(50).copy _
Destination:=bkDest
bksrc.close SaveChanges:=False
bkDest.close SaveChanges:=True
Next
End Sub
If you had come sequence to our names and some
corresondence between source
and destination filenames, this could be simpler.
--
Regards,
Tom Ogilvy
"KENNY" wrote in
message
...
What if I know precisely the name of the destination
file,
as well as its location (Lookup?)?
-----Original Message-----
This can definitely be done in a macro if you are
willing
to start the
macro and then find the destination file in an open
prompt...the macro
would then open that file...put in the 50 rows (how
many
columns?)...and then close and save the file.
.
.
|