Copy data from one workbook to another of same name
 

Greetings All,

Here is my problem: I need to copy the first 50 rows of a
workbook and paste it into another of a similar name.
More specifically, I have a folder of files that each need
to have their first 50 rows copied and pasted into files
that reside in another folder. Somehow I need to ensure
that File A in Folder One, pastes those 50 rows into File
A of Folder Two..... and so on through File Z.

Am I drunk, and/or can this be done?!!

Copy data from one workbook to another of same name
 

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.

Copy data from one workbook to another of same name
 

I'm a bit desperate, so yes.... At around 100 files, and
the close and save being automated (?), shouldn't take too
long, yes?

Anyway, I'm all ears if you can point me in the direction
to start....

TIA!


-----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.

.

Copy data from one workbook to another of same name
 

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.

.

Copy data from one workbook to another of same name
 

Sub TEMP()
'OPEN WORKBOOK
Application.ScreenUpdating = False
Application.DisplayAlerts = False
current = Application.ActiveWorkbook.Name
Dim FName As Variant
FName = Application.GetOpenFilename _
(filefilter:="Excel Files(*.xls),*.xls,All Files (*.*),*.*")

If FName = False Then
Exit Sub
Else
Workbooks.OpenText Filename:=FName
End If
Dim TEMP As Variant
openedwkbk = Application.ActiveWorkbook.Name

'COPY
Workbooks(current).Activate
Rows("1:50").Copy
Workbooks(openedwkbk).Activate
Range("A1").Select
ActiveSheet.Paste

'SAVE AND CLOSE
ActiveWorkbook.SaveAs Filename:=FName, FileFormat:=xlNormal
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

Copy data from one workbook to another of same name
 

There is a start...FNAME is the full path name of the files...but it
may be more work to list those out then just to click on all the files.

Copy data from one workbook to another of same name
 

My bad, get rid of the line that reads "Dim TEMP as Variant"

Copy data from one workbook to another of same name
 

While I very much appreciate the response, I fear I may be
in over my head here...

Do I list all the files I wish to open after FName =
Application.GetOpenFilename _?




-----Original Message-----
Sub TEMP()
'OPEN WORKBOOK
Application.ScreenUpdating = False
Application.DisplayAlerts = False
current = Application.ActiveWorkbook.Name
Dim FName As Variant
FName = Application.GetOpenFilename _
(filefilter:="Excel Files(*.xls),*.xls,All Files
(*.*),*.*")

If FName = False Then
Exit Sub
Else
Workbooks.OpenText Filename:=FName
End If
Dim TEMP As Variant
openedwkbk = Application.ActiveWorkbook.Name

'COPY
Workbooks(current).Activate
Rows("1:50").Copy
Workbooks(openedwkbk).Activate
Range("A1").Select
ActiveSheet.Paste

'SAVE AND CLOSE
ActiveWorkbook.SaveAs Filename:=FName,
FileFormat:=xlNormal
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub

.

Copy data from one workbook to another of same name
 

The code I gave you will do what I said earlier:
"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."

IF you need to have the macro open the files (and not you) that gets
much more complicated.

Copy data from one workbook to another of same name
 

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.

.

Copy data from one workbook to another of same name
 

Thanks for the reply : The source and destination files
are exactly the same with one exception: the destination
files have an "M" at the end, e.g.

Source = TR123
Destination = TR123M

Does that help? Thanks a ton!


-----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.

.



.

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.

.



.

Copy data from one workbook to another of same name
 

There was a problem on one line. I fixed that.

This uses the fact about just adding the M, so it only needs the srclist. I
tested it and it worked for me:

Sub RAW_AA()

Dim PathSrc As String, PathDest As String
Dim srcList As Variant
Dim i As Long, sDest As String
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", _
"Raw 3.xls")



For i = LBound(srcList) To UBound(srcList)
Set bkSrc = Workbooks.Open(PathSrc & srcList(i))
sDest = bkSrc.Name
sDest = Left(sDest, Len(sDest) - 4) & "M.xls"
Set bkDest = Workbooks.Open(PathDest & sDest)
bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _
Destination:=bkDest.Worksheets(1).Range("A1")
bkSrc.Close SaveChanges:=False
bkDest.Close SaveChanges:=True
Next

End Sub

--------------
This uses two lists. Also tested and it works.

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", _
"Raw 3.xls")

destList = Array("Raw 1M.xls", _
"Raw 2M.xls", _
"Raw 3M.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.Worksheets(1).Range("A1")
bkSrc.Close SaveChanges:=False
bkDest.Close SaveChanges:=True
Next

End Sub

--
Regards,
Tom Ogilvy


"KENNY" wrote in message
...
If I haven't burnt all my goodwill, I'm now get the error:
"Copy Method of Rangle Class Failed"
-----Original Message-----
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.

.



.

.

Copy data from one workbook to another of same name
 

SUCCESS!!!

I can't begin to tell you how much I appreciate your
help... I think you MVP's that save us hacks are AWESOME!




-----Original Message-----
There was a problem on one line. I fixed that.

This uses the fact about just adding the M, so it only
needs the srclist. I
tested it and it worked for me:

Sub RAW_AA()

Dim PathSrc As String, PathDest As String
Dim srcList As Variant
Dim i As Long, sDest As String
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", _
"Raw 3.xls")



For i = LBound(srcList) To UBound(srcList)
Set bkSrc = Workbooks.Open(PathSrc & srcList(i))
sDest = bkSrc.Name
sDest = Left(sDest, Len(sDest) - 4) & "M.xls"
Set bkDest = Workbooks.Open(PathDest & sDest)
bkSrc.Worksheets(1).Rows(1).Resize(50).Copy _
Destination:=bkDest.Worksheets(1).Range("A1")
bkSrc.Close SaveChanges:=False
bkDest.Close SaveChanges:=True
Next

End Sub

--------------
This uses two lists. Also tested and it works.

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", _
"Raw 3.xls")

destList = Array("Raw 1M.xls", _
"Raw 2M.xls", _
"Raw 3M.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.Worksheets(1).Range("A1")
bkSrc.Close SaveChanges:=False
bkDest.Close SaveChanges:=True
Next

End Sub

--
Regards,
Tom Ogilvy


"KENNY" wrote in
message
...
If I haven't burnt all my goodwill, I'm now get the
error:
"Copy Method of Rangle Class Failed"
-----Original Message-----
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.

.



.

.



.